1. Forum
  2. Usenet
  3. COMP.DATABASES.INGRES

  • OLD_COPY_DN gotcha (or rather, got my customer)

    From Roy Hann@21:1/5 to All on Tue Aug 29 08:24:08 2023
    Anyone who still hangs out here will know I was doing an 11.2 upgrade
    for a customer over the weekend.

    It all went smoothly, except one job that had made it through UAT but
    started failing when they went live. The job used COPY...FROM to load
    data from flat files. The format specification used the "d" (dummy) specification to skip junk.

    It has come to light that the behaviour of the dummy specification
    changed in 11.1. You can read about it by looking up SET SESSION
    OLD_COPY_DN in the SQL Guide. As far as I can tell you can't read about
    it anywhere else...like say, in the Installation Guide.

    Roy

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From G Jones@21:1/5 to All on Wed Aug 30 00:09:42 2023
    It's in the 'patchbugs' doc, though surprisingly not marked with "attention". On reading that bug description again the last bit worries me.
    "Please note that using d0 and text(0) continues to be unsafe. They produces undefined behaviour".
    Hmm. We use 'text(0)' quite frequently.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Colin Hay@21:1/5 to G Jones on Wed Aug 30 04:04:23 2023
    On Wednesday, 30 August 2023 at 17:09:44 UTC+10, G Jones wrote:
    It's in the 'patchbugs' doc, though surprisingly not marked with "attention". On reading that bug description again the last bit worries me.
    "Please note that using d0 and text(0) continues to be unsafe. They produces undefined behaviour".
    Hmm. We use 'text(0)' quite frequently.


    We use d0 quite frequently too - whats the alternative ?

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Roy Hann@21:1/5 to Colin Hay on Wed Aug 30 12:19:55 2023
    Colin Hay wrote:

    On Wednesday, 30 August 2023 at 17:09:44 UTC+10, G Jones wrote:
    It's in the 'patchbugs' doc, though surprisingly not marked with "attention".
    On reading that bug description again the last bit worries me.
    "Please note that using d0 and text(0) continues to be unsafe. They produces undefined behaviour".
    Hmm. We use 'text(0)' quite frequently.

    We use d0 quite frequently too - whats the alternative ?

    SET SESSION OLD_COPY_DN;

    Or as Paul Mason points out, you can configure it globally in CBF. You
    may need to first do:

    iiinitres old_copy_dn

    Roy

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Paul White@21:1/5 to G Jones on Tue Sep 12 22:00:56 2023
    On Wednesday, August 30, 2023 at 5:09:44 PM UTC+10, G Jones wrote:
    It's in the 'patchbugs' doc, though surprisingly not marked with "attention".
    On reading that bug description again the last bit worries me.
    "Please note that using d0 and text(0) continues to be unsafe. They produces undefined behaviour".
    Hmm. We use 'text(0)' quite frequently.

    I found these entry in Patch Bugs

    First mentioned in P15477

    Bug 135261 II-4611 (ATTENTION, GENERIC)
    Add a CBF parameter 'old_copy_dn' which reverts the default behaviour of a copy dummy format to the behaviour prior to the fix for bug 134443.
    Previously a format of "col1 = Dn" would skip over n characters when doing a COPY FROM. However a COPY INTO would write the column name ("col1") n times. The fix for 134443 made these the same so that COPY FROM now skips n * length of column name (n * 4
    in this example).
    After applying this patch the new parameter is available. To add it to an existing installation first create it with the following command:

    iiinitres old_copy_dn

    This will add it with its default value of OFF. To enable the old behaviour set it to ON in CBF.
    This behaviour can also be changed at the session level using:
    SET SESSION [NO]OLD_COPY_DN



    And from P15682
    Bug 138448 II-7901 (GENERIC)

    COPY column formats d0 and text(0) must have a delimiter defined to be able to be safely unloaded and reloaded. For backwards compatibility the old_copy_dn config paramter and the "SET SESSION OLD_COPY_DN" statements were added. With the old behaviour
    selected some COPY statements still fail with "E_US16F0 COPY: Column 'nl', format 'd0' requires a delimiter." Please note that using d0 and text(0) continues to be unsafe. They produces undefined behaviour and reloads are likely to fail.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)