To add,
I ran the following script on a test environment (non-local) and was able
to reproduce the issue:
DB.create_table(:foo){Integer :id; Time :t}
ds = DB[:foo]
ds.insert(1, '2021-11-07 01:35:44.443726')
ds.insert(2, '2022-02-21 08:22:37.146317')
ds.insert(3, '2021-03-23 08:36:22.655691')
p ds.where(id: 1).get(:t)
p ds.where(id: 2).get(:t)
p ds.where(id: 1).get(:t)
p ds.where(id: 3).get(:t)
p ds.where(id: 1).get(:t)
*2021-11-07 01:35:44.443726 -0700*
2022-02-21 08:22:37.146317 -0800
*2021-11-07 01:35:44.443726 -0800*
2021-03-23 08:36:22.655691 -0700
*2021-11-07 01:35:44.443726 -0700*
I will try to figure out what the difference between my test environment
and local environment is
On Tuesday, September 27, 2022 at 6:03:10 PM UTC-4 Charlie Pham wrote:
> Ah, shoot.
>
> I'm unable to reproduce this on my local database but am able to reproduce
> it in another setting.
>
> When trying to monkey patch on my local with the following code:
>
> module Sequel
> module Postgres
> class Dataset
>
> def fetch_rows(sql)
> return cursor_fetch_rows(sql){|h| yield h} if @opts[:cursor]
> execute(sql) do |res|
> cols = fetch_rows_set_cols(res)
> *puts cols*
> yield_hash_rows(res, cols) do |h| yield h
> end
> end
> end
> end
> end
> end
>
> The bolded line correctly puts out #<Method:
> Sequel::Postgres::Database(Sequel::Database)#to_application_timestamp(v)
> /Users/charliepham/.rbenv/versions/3.1.2/lib/ruby/gems/3.1.0/gems/sequel-5.60.1/lib/sequel/database/misc.rb:311>
>
>
> Which always returns that consistent value.
>
> When I run it on the environment that has the issue, `cols` is actually
> nil! I thought that it was `pg_sequel` that was overwriting these methods
> (fetch_rows_set_cols, yield_hash_rows, etc.). Is that correct? Or perhaps
> there's another plugin I'm neglecting...
> On Tuesday, September 27, 2022 at 5:27:34 PM UTC-4 Jeremy Evans wrote:
>
>> On Tue, Sep 27, 2022 at 2:08 PM 'Charlie Pham' via sequel-talk <
>> [email protected]> wrote:
>>
>>> Hello,
>>>
>>> I have a model that has a field `created_at` that is of type timestamp
>>> without timezone in Postgres. All of the relevant timezone settings are set
>>> to `:local`. We are in PST (-8) and PDT (-7).
>>>
>>> I am running into an issue that I believe stems from `pg_sequel` that
>>> results in an ambiguous (during the hour where daylight savings ends) time
>>> value in Ruby flip-flopping between the -7 and -8 timezones.
>>>
>>> Observe the behaviour here:
>>> *Model**[132639966].changed_at => 2021-11-07 01:35:44.443726 -0700 *
>>> Model[150890643].changed_at => 2022-02-21 08:22:37.146317 -0800
>>> *Model**[132639966].changed_at => 2021-11-07 01:35:44.443726 -0800*
>>> Model.first.changed_at => 2021-03-23 08:36:22.655691 -0700
>>> *Model**[132639966].changed_at
>>> => 2021-11-07 01:35:44.443726 -0700*
>>>
>>> It appears that whether the -7 or -8 appears depends on the last
>>> encountered value (see the previous lines). Repeating any of the bold lines
>>> sequentially yields the same result, but running any of the non-bolded
>>> lines immediately changes the bold lines' outputs to match the timezone of
>>> the non-bolded lines.
>>>
>>> I believe the source of this is pg_sequel since patching the vanilla
>>> `fetch_rows_set_cols` and `yield_hash_rows` results in consistent behaviour
>>> (always -8)
>>> *Model[132639966].changed_at => 2021-11-07 01:35:44.443726 -0800*
>>> Model[150890643].changed_at => 2022-02-21 08:22:37.146317 -0800
>>> *Model**[132639966].changed_at => 2021-11-07 01:35:44.443726 -0800*
>>> Model.first.changed_at => 2021-03-23 08:36:22.655691 -0700
>>> *Model**[132639966].changed_at => 2021-11-07 01:35:44.443726 -0800*
>>>
>>> I tried looking at the source code for handling timestamp columns in
>>> the c code but can't figure it out.
>>>
>>> Best,
>>> Charlie
>>>
>>
>> Charlie,
>>
>> Are the underlying columns timestamp or timestamptz (timestamp with
>> timezone)? If they are timestamp and not timestamptz, they are stored in
>> local time, and local time uses daylight savings time, then the underlying
>> values are ambiguous. Why exactly they flip-flop, I'm not sure, but in any
>> case, the result would not be considered a bug as the underlying values are
>> ambiguous.
>>
>> Is this issue specific to sequel_pg? If you run sequel without sequel_pg
>> (NO_SEQUEL_PG in the environment will disable the use of sequel_pg), do you
>> get consistent results? I'm not seeing anything specific in sequel_pg that
>> would cause this, but maybe it is using a Ruby C-API that does this
>> (rb_time_timespec_new maybe?).
>>
>> If you could submit a minimal self contained example showing this issue,
>> that would significantly speed up debugging. Something like:
>>
>> DB.create_table(:foo){Integer :id; Time :t}
>> ds = DB[:foo]
>> ds.insert(1, '*2021-11-07 01:35:44.443726*')
>> ds.insert(2, ' 2022-02-21 08:22:37.146317')
>> p ds.where(id: 1).get(:t)
>> p ds.where(id: 2).get(:t)
>> p ds.where(id: 1).get(:t)
>>
>> That example doesn't show the issue in my environment, though:
>>
>> 2021-11-07 01:35:44 -0700
>> 2022-02-21 08:22:37 -0800
>> 2021-11-07 01:35:44 -0700
>>
>> Does it show the issue in your environment? If not, hopefully you can
>> modify it so that it does show the issue.
>>
>> Thanks,
>> Jeremy
>>
>
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/a0b735ae-ffc1-45bf-b5e7-3b461d0333d7n%40googlegroups.com.
