[GENERAL] idle processes
I have read quite a variety of stuff on the internet about an explanation for idle postgresql processes but still do not understand the following typical scenario. This is on Debian (postgresql 9.4.4-1.pgdg80+1). Running the following (as user crest) on an empty table using psql: select * from wos.person; Shows that there are no data in the table. In psql the process ends and psql is ready for the next query. However, much later one sees something like this when querying pg_stat_activity: "book_citation";"postgres";3358;"";f;"2015-08-05 09:00:02.503117+02";"select * from wos.person;" Now the process is indicated as one run by the user postgres. As user postgres: postgres=# select pg_cancel_backend(3358); pg_cancel_backend --- t (1 row) And I can repeat this but ps (or pg_stat_activity) shows process 3358. Now my questions: Why does this happen? Should I do something about it? If so, what? Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3)
[GENERAL] json ->> operator precedence
An interesting quirk:
# select CASE WHEN '{"a":null}'::jsonb->>'a' IS NULL THEN 'yes' ELSE 'no'
END;
case
--
yes
According to the precedence table
http://www.postgresql.org/docs/9.4/static/sql-syntax-lexical.html I would
expect ->> to come under "all other native and user-defined operators",
which would imply that this command should be testing whether 'a' IS NULL
and applying the result (false) to the json operator - at which point we
have
# SELECT CASE WHEN '{"a":null}'::jsonb->>false THEN 'yes' ELSE 'no' END;
and since
# SELECT '{"a":null}'::jsonb->>false;
returns NULL, the query is effectively:
# SELECT CASE WHEN NULL THEN 'yes' ELSE 'no' END;
which returns 'no'.
So the only way that we should get 'yes' is if the ->> has higher
precedence than 'IS NULL'.
OK, so be it; except if we assume that the reason is because the lex
analyzer sees '-' and assumes higher precedence than 'IS NULL' then you
would expect
SELECT '{"a":10}'::jsonb->>'a' - 5;
to return '5' - since left-to-right precedence would make ->> run before
the subtraction; however I get:
ERROR: invalid input syntax for integer: "a"
LINE 1: select '{"a":10}'::jsonb->>'a' - 5;
So what precedence level is ->> actually running at?
Or am I missing something?
Cheers
Geoff
Re: [GENERAL] json ->> operator precedence
On Wed, Aug 5, 2015 at 5:02 AM, Geoff Winkless wrote:
> An interesting quirk:
>
> # select CASE WHEN '{"a":null}'::jsonb->>'a' IS NULL THEN 'yes' ELSE 'no'
> END;
> case
> --
> yes
>
> According to the precedence table
> http://www.postgresql.org/docs/9.4/static/sql-syntax-lexical.html I would
> expect ->> to come under "all other native and user-defined operators",
> which would imply that this command should be testing whether 'a' IS NULL
> and applying the result (false) to the json operator - at which point we
> have
>
> # SELECT CASE WHEN '{"a":null}'::jsonb->>false THEN 'yes' ELSE 'no' END;
>
> and since
>
> # SELECT '{"a":null}'::jsonb->>false;
>
> returns NULL, the query is effectively:
>
> # SELECT CASE WHEN NULL THEN 'yes' ELSE 'no' END;
>
> which returns 'no'.
>
> So the only way that we should get 'yes' is if the ->> has higher
> precedence than 'IS NULL'.
>
> OK, so be it; except if we assume that the reason is because the lex
> analyzer sees '-' and assumes higher precedence than 'IS NULL' then you
> would expect
>
> SELECT '{"a":10}'::jsonb->>'a' - 5;
>
> to return '5' - since left-to-right precedence would make ->> run before
> the subtraction; however I get:
>
> ERROR: invalid input syntax for integer: "a"
> LINE 1:
>
> select '{"a":10}'::jsonb->>'a' - 5;
>
> So what precedence level is ->> actually running at?
>
> Or am I missing something?
>
Looks correct to me. As I understand it the ::jsonb is NOT an operator! It
is a syntactic construct for a CAST(). An equivalent which might make more
sense is:
select CASE WHEN CAST('{"a":null}' AS JSONB)->>'a' IS NULL THEN 'yes' ELSE
'no' END;
Oh, an CAST() may look like a function call, but it is also a syntactic
element. I.e. there is not a function called "CAST".
> Cheers
>
> Geoff
>
--
Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.
Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.
He's about as useful as a wax frying pan.
10 to the 12th power microphones = 1 Megaphone
Maranatha! <><
John McKown
Re: [GENERAL] idle processes
On 08/05/2015 12:38 AM, Johann Spies wrote: I have read quite a variety of stuff on the internet about an explanation for idle postgresql processes but still do not understand the following typical scenario. This is on Debian (postgresql 9.4.4-1.pgdg80+1). Running the following (as user crest) on an empty table using psql: select * from wos.person; Shows that there are no data in the table. In psql the process ends and psql is ready for the next query. However, much later one sees something like this when querying pg_stat_activity: "book_citation";"postgres";3358;"";f;"2015-08-05 09:00:02.503117+02";"select * from wos.person;" Now the process is indicated as one run by the user postgres. As user postgres: postgres=# select pg_cancel_backend(3358); pg_cancel_backend --- t (1 row) And I can repeat this but ps (or pg_stat_activity) shows process 3358. Now my questions: Why does this happen? Should I do something about it? If so, what? Depends on what you are trying to accomplish. From here: http://www.postgresql.org/docs/9.4/static/functions-admin.html pg_cancel_backend "Cancel a backend's current query. You can execute this against another backend that has exactly the same role as the user calling the function. In all other cases, you must be a superuser." So it will just cancel a running query, not get rid of the process. If you want to do that, then: pg_terminate_backend "Terminate a backend. You can execute this against another backend that has exactly the same role as the user calling the function. In all other cases, you must be a superuser." Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3) -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] json ->> operator precedence
On 5 August 2015 at 14:35, John McKown wrote: > > Looks correct to me. As I understand it the ::jsonb is NOT an operator! > It is a syntactic construct for a CAST(). An equivalent which might make > more sense is: > My issue is nothing to do with the ::jsonb cast, it's the precedence of the ->> operator. As far as I can see the ->> operator has predence below '- +' but above 'IS', but there's no entry for it in that place in the precedence table. Geoff
Re: [GENERAL] json ->> operator precedence
On Wed, Aug 5, 2015 at 8:35 AM, John McKown
wrote:
>
>
> Looks correct to me. As I understand it the ::jsonb is NOT an operator!
> It is a syntactic construct for a CAST(). An equivalent which might make
> more sense is:
>
> select CASE WHEN CAST('{"a":null}' AS JSONB)->>'a' IS NULL THEN 'yes' ELSE
> 'no' END;
>
> Oh, an CAST() may look like a function call, but it is also a syntactic
> element. I.e. there is not a function called "CAST".
>
>
Well, I messed that up a bit. CAST is not a "syntactic element" as I was
thinking. But it's not really a function call either but is closer to a
function call than an operator in nature (I my ignorant opinion) . I was
thinking it was a "compile time" operation, but it, like a function call,
is a run-time operation. I think that using the CASE() makes it plainer
that it is _not_ an operation like ->> or - and makes the precedence issue
plainer.
--
Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.
Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.
He's about as useful as a wax frying pan.
10 to the 12th power microphones = 1 Megaphone
Maranatha! <><
John McKown
Re: [GENERAL] idle processes
Johann Spies writes: > I have read quite a variety of stuff on the internet about an explanation > for idle postgresql processes but still do not understand the following > typical scenario. > This is on Debian (postgresql 9.4.4-1.pgdg80+1). > Running the following (as user crest) on an empty table using psql: > select * from wos.person; > Shows that there are no data in the table. In psql the process ends and > psql is ready for the next query. However, much later one sees something > like this when querying pg_stat_activity: > "book_citation";"postgres";3358;"";f;"2015-08-05 > 09:00:02.503117+02";"select * from wos.person;" It's not entirely clear what your question is, but here are some possible answers: 1. For quite some time now, the "query" column in pg_stat_activity has been defined as "the query currently or most recently run by the session"; it's intentional that it doesn't go back to "" anymore. You need to look at the "state" column to tell whether the session is actively running the query or not. 2. Are you sure that operating system user "crest" isn't connecting as database user "postgres"? 3. pg_cancel_backend() is only supposed to terminate the current query (if any), not kill the session. If you want the latter try pg_terminate_backend(). > Why does this happen? > Should I do something about it? If so, what? AFAICS you have not shown anything behaving out of the ordinary. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] json ->> operator precedence
On Wed, Aug 5, 2015 at 8:42 AM, Geoff Winkless wrote: > On 5 August 2015 at 14:35, John McKown > wrote: >> >> Looks correct to me. As I understand it the ::jsonb is NOT an operator! >> It is a syntactic construct for a CAST(). An equivalent which might make >> more sense is: >> > > My issue is nothing to do with the ::jsonb cast, it's the precedence of > the ->> operator. > > As far as I can see the ->> operator has predence below '- +' but above > 'IS', but there's no entry for it in that place in the precedence table. > Ah. I see your point now. Sorry about that. > > Geoff > > -- Schrodinger's backup: The condition of any backup is unknown until a restore is attempted. Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! <>< John McKown
Re: [GENERAL] json ->> operator precedence
Geoff Winkless writes:
> An interesting quirk:
> # select CASE WHEN '{"a":null}'::jsonb->>'a' IS NULL THEN 'yes' ELSE 'no'
> END;
> case
> --
> yes
Apparently you're running that on 9.5 or HEAD.
> According to the precedence table
> http://www.postgresql.org/docs/9.4/static/sql-syntax-lexical.html I would
> expect ->> to come under "all other native and user-defined operators",
It does ...
> which would imply that this command should be testing whether 'a' IS NULL
> and applying the result (false) to the json operator - at which point we
> have
... and in 9.4 that's what happens:
regression=# select CASE WHEN '{"a":null}'::jsonb->>'a' IS NULL THEN 'yes' ELSE
'no'
END;
ERROR: operator does not exist: jsonb ->> boolean
LINE 1: select CASE WHEN '{"a":null}'::jsonb->>'a' IS NULL THEN 'yes...
^
HINT: No operator matches the given name and argument type(s). You might need
to add explicit type casts.
> Or am I missing something?
The first compatibility item in the 9.5 release notes: we changed
the precedence of IS and some other things. You need to be reading
the 9.5 version of the precedence table.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] json ->> operator precedence
On 5 August 2015 at 14:52, Tom Lane wrote: > > The first compatibility item in the 9.5 release notes: we changed > the precedence of IS and some other things. You need to be reading > the 9.5 version of the precedence table. Doh. Sorry, I'm an idiot.
Re: [GENERAL] Question about copy from with timestamp format
Hi everyone, First of all, let me thank all of you for the very informative discussion. I will say my solution was to declare the field MMDDHH24 as int (can handle till Dec 31, 2147, Hr23 -- which will be 2147123123). Also this way, I can still use between etc to select a range of dates.. of course, I will miss validation.. I believe it will work for me to the best of my knowledge. (let me know if you have experiences with storing time as int and there are issues I have not thought of).. thanks, murali. On Tue, Aug 4, 2015 at 10:22 PM, Jim Nasby wrote: > On 7/30/15 3:09 PM, Alban Hertroys wrote: > >> COPY is a bit special, I'm afraid. For starters, although it works_like_ >> doing a bunch of INSERTs, it doesn't perform actual INSERTs. Apparently, >> that also means it won't fire an INSERT rule and thus can't be used with an >> updatable view. There are no rules on such a view (rules rewrite the query) >> that would work for COPY. >> >> Now perhaps that sounds like a COPY rule is warranted for cases like >> these, but that doesn't help, exactly because the COPY command has no place >> in its syntax for expressions (such as this type conversion). INSERT does, >> hence we can write a rule for it… >> >> In hindsight it all makes sense. That doesn't bring you any closer to a >> solution, unfortunately. >> > > By the way, if you're desperate enough to make this work during copy, you > could create a new type that understands that time format. It'd involve > some C coding though. > > It would be nice if there was a way to do transforms during COPY. I > vaguely remember some discussion of that on hackers some time ago. > -- > Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX > Data in Trouble? Get it in Treble! http://BlueTreble.com >
Re: [GENERAL] Question about copy from with timestamp format
On 08/05/2015 09:16 AM, Murali M wrote:
Hi everyone,
First of all, let me thank all of you for the very informative
discussion. I will say my solution was to declare the field MMDDHH24
as int (can handle till Dec 31, 2147, Hr23 -- which will be 2147123123).
Also this way, I can still use between etc to select a range of dates..
of course, I will miss validation.. I believe it will work for me to the
best of my knowledge. (let me know if you have experiences with storing
time as int and there are issues I have not thought of)..
postgres@production=# select to_date('201508051314', 'MMDDHH24MI') -
to_date('201508041314', 'MMDDHH24MI');
?column?
--
1
(1 row)
postgres@production=# select 201508051314 - 201508041314;
?column?
--
1
(1 row)
postgres@production=# select 201508051314::timestamp -
201508041314::timestamp;
ERROR: cannot cast type bigint to timestamp without time zone
LINE 1: select 201508051314::timestamp - 201508041314::timestamp;
So it comes down to where you want to spend the time, doing a one time
convert on import or do conversions every time you want to use the data
as a timestamp instead of the type it is stored as.
thanks, murali.
--
Adrian Klaver
adrian.kla...@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
