Fwd: [GENERAL] Array Comparison
Please send replies to the list.
On Friday, December 5, 2014, Ian Harding > wrote:
>
>
> On Fri, Dec 5, 2014 at 5:37 PM, David G Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> Ian Harding wrote
>> > On Fri, Dec 5, 2014 at 4:55 PM, Ian Harding <
>>
>> > harding.ian@
>>
>> > > wrote:
>> >> I have a function that returns bigint[] and would like to be able to
>> >> compare a bigint to the result.
>>
>> Here are some of your options:
>>
>> http://www.postgresql.org/docs/9.3/interactive/functions-array.html
>> http://www.postgresql.org/docs/9.3/interactive/functions-comparisons.html
>>
>> The direct type-to-type operators are covered in the first link while
>> generic comparison mechanisms - including those the can compare arrays to
>> scalars - are in the second one.
>>
>> There are lots of ways to compare things; e.g., are they equal, is one
>> greater than another and, for multi-valued items, does one contain the
>> other
>> or do they overlap
>>
>>
>> >> select 935::bigint in (select
>> >> fn_descendents('trip'::varchar,61::bigint));
>> >> ERROR: operator does not exist: bigint = bigint[]
>>
>> As shown by the error the application of "IN" simply checks to see if any
>> of
>> the ROWS of the given select match against the left-hand value. That
>> means
>> zero or more evaluations of:
>> bigint = bigint[]
>> which does not makes sense. There is no special evalulation mode for a
>> subquery that only happens to return a single row.
>>
>> Ah. Right. That makes sense.
>
>
>> From the second link above you can express the scalar-to-array comparison
>> you seek through the use of "ANY".
>>
>> bigint = ANY(bigint[])
>>
>> Since your function already returns an array you do not to (and indeed
>> cannot) use a subquery/SELECT. Simply write:
>>
>> 935::bigint = ANY(fn_descendents(...))
>>
>>
>> I sort of figured that out only I fatfingered it to "... IN ANY(..."
>
>
>> >> Hmmm.. This works...
>> >>
>> > select array[935::bigint] <@ (select
>> > fn_descendents('trip'::varchar,61::bigint));
>> >
>> > Still, why?
>>
>> Do you understand the concept of array containment - what it means for an
>> array to contain or be contained by another array? The documentation
>> assumes that concept is known and simply provides the syntax/operators
>> needed to access it.
>>
>>
> Ah, but isn't this the same "There is no special evalulation mode for a
> subquery that only happens to return a single row." from above? I'm asking
> "Is this scalar array contained in the result set of this select for which
> there is no special evaluation mode for the happy coincidence that it only
> has one value? That's what surprised me.
>
>
No. Your query will output one row for every input row the subquery
generates - each row having a true or false value depending on whether the
particular value contains your array constant.
Your initial attempt incorrectly tried to get in the IN to apply to each
element of the only array that was returned but that doesn't work and which
is why the scalar=array comparison failed; the array is never decomposed.
If your replace <@ with IN in this example you would get a single result
(Boolean false in this case) regardless of how many rows the subquery
returns. The IN wraps the subquery expression and makes it into a kind of
scalar while directly using the operator against the subquery causes
multiple evaluations.
See: SELECT generate_series(1,10) - for an idea of how row generating
expressions in the select list behave.
David J.
Re: [GENERAL] Array Comparison
Ian Harding wrote
> On Fri, Dec 5, 2014 at 4:55 PM, Ian Harding <
> harding.ian@
> > wrote:
>> I have a function that returns bigint[] and would like to be able to
>> compare a bigint to the result.
Here are some of your options:
http://www.postgresql.org/docs/9.3/interactive/functions-array.html
http://www.postgresql.org/docs/9.3/interactive/functions-comparisons.html
The direct type-to-type operators are covered in the first link while
generic comparison mechanisms - including those the can compare arrays to
scalars - are in the second one.
There are lots of ways to compare things; e.g., are they equal, is one
greater than another and, for multi-valued items, does one contain the other
or do they overlap
>> select 935::bigint in (select
>> fn_descendents('trip'::varchar,61::bigint));
>> ERROR: operator does not exist: bigint = bigint[]
As shown by the error the application of "IN" simply checks to see if any of
the ROWS of the given select match against the left-hand value. That means
zero or more evaluations of:
bigint = bigint[]
which does not makes sense. There is no special evalulation mode for a
subquery that only happens to return a single row.
>From the second link above you can express the scalar-to-array comparison
you seek through the use of "ANY".
bigint = ANY(bigint[])
Since your function already returns an array you do not to (and indeed
cannot) use a subquery/SELECT. Simply write:
935::bigint = ANY(fn_descendents(...))
>> Hmmm.. This works...
>>
> select array[935::bigint] <@ (select
> fn_descendents('trip'::varchar,61::bigint));
>
> Still, why?
Do you understand the concept of array containment - what it means for an
array to contain or be contained by another array? The documentation
assumes that concept is known and simply provides the syntax/operators
needed to access it.
David J.
--
View this message in context:
http://postgresql.nabble.com/Array-Comparison-tp5829471p5829473.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] Array Comparison
On Fri, Dec 5, 2014 at 4:55 PM, Ian Harding wrote:
> I have a function that returns bigint[] and would like to be able to
> compare a bigint to the result.
>
> freeload=> select fn_descendents('trip'::varchar,61::bigint);
> fn_descendents
> ---
> {935,815,689,569,446,325,205,191}
> (1 row)
> freeload=> select 935::bigint in (select
> fn_descendents('trip'::varchar,61::bigint));
> ERROR: operator does not exist: bigint = bigint[]
> LINE 1: select 935::bigint in (select fn_descendents('trip'::varchar...
>^
> HINT: No operator matches the given name and argument type(s). You might
> need to add explicit type casts.
> This is probably super obvious but I'm not getting it right now.
>
> Hmmm.. This works...
>
select array[935::bigint] <@ (select
fn_descendents('trip'::varchar,61::bigint));
Still, why?
[GENERAL] Array Comparison
I have a function that returns bigint[] and would like to be able to
compare a bigint to the result.
freeload=> select fn_descendents('trip'::varchar,61::bigint);
fn_descendents
---
{935,815,689,569,446,325,205,191}
(1 row)
freeload=> select 935::bigint in (select
fn_descendents('trip'::varchar,61::bigint));
ERROR: operator does not exist: bigint = bigint[]
LINE 1: select 935::bigint in (select fn_descendents('trip'::varchar...
^
HINT: No operator matches the given name and argument type(s). You might
need to add explicit type casts.
This is probably super obvious but I'm not getting it right now.
Thank you all in advance!
- Ian
Re: [GENERAL] autovacuum with inheritance
yhe wrote > From looking at the log, [...] Any reason not to share it with the people from whom you are asking for help? > I was thinking that autovacuum should only happen on updated table so it > should only autovacuum on one child and not the others for my case. At the point of "stop usage" on the partition it would still want at least one more cleanup vacuum; and eventually would want a FREEZE pass as well. Furthermore, the parent of the partition scheme still needs to be aware of the children and so you might be seeing that. David J. -- View this message in context: http://postgresql.nabble.com/autovacuum-with-inheritance-tp5829450p5829461.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] Multiple call handlers per language
Kelly Burkhart writes: > Hello, our production database has existed for quite a few years and been > dumped/restored several times for hardware or postgresql upgrades. > Original version was late 7 or early 8, we're currently on 8.4.2. I > noticed on our production database I have two call handlers for plpgsql and > for plpython; the following query: You could presumably drop the ones in the public schema. Probably those are leftover from ancient history when these things were not getting created in pg_catalog. > Should I worry about the extra rows in our production database? If so how > should I go about cleaning them? DROP FUNCTION (as a superuser) would be the safest route. I'm pretty sure the dependency system would prevent you from dropping the wrong ones (the ones the language definitions are actually using); though you might want to verify that in a scratch copy before you do it in the production database. 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
[GENERAL] Multiple call handlers per language
Hello, our production database has existed for quite a few years and been dumped/restored several times for hardware or postgresql upgrades. Original version was late 7 or early 8, we're currently on 8.4.2. I noticed on our production database I have two call handlers for plpgsql and for plpython; the following query: select pn.nspname, pu0.usename as nspowner, pp.proname, pu1.usename as proowner, pp.prosrc, pp.probin from pg_proc pp, pg_namespace pn, pg_user pu0, pg_user pu1 where pp.proname like '%call_handler%' and pn.oid = pp.pronamespace and pu0.usesysid = pn.nspowner and pu1.usesysid = pp.proowner order by pp.proname; Produces this: nspname | nspowner |proname| proowner | prosrc | probin +--+---+--+---+-- pg_catalog | postgres | plpgsql_call_handler | postgres | plpgsql_call_handler | $libdir/plpgsql public | postgres | plpgsql_call_handler | postgres | plpgsql_call_handler | $libdir/plpgsql pg_catalog | postgres | plpython_call_handler | postgres | plpython_call_handler | $libdir/plpython public | postgres | plpython_call_handler | postgres | plpython_call_handler | $libdir/plpython (4 rows) When I run this on a fresh database created on a fresh cluster like this: createdb --template=template1 krbtst createlang plpythonu krbtst I see this: nspname | nspowner |proname| proowner | prosrc | probin +--+---+--+---+-- pg_catalog | postgres | plpgsql_call_handler | postgres | plpgsql_call_handler | $libdir/plpgsql pg_catalog | postgres | plpython_call_handler | postgres | plpython_call_handler | $libdir/plpython (2 rows) Should I worry about the extra rows in our production database? If so how should I go about cleaning them? -K
Re: Fwd: Fwd: [GENERAL] Problem with pg_dump and decimal mark
On 12/05/2014 01:13 AM, Eric Svenson wrote: Hi Adrian, Is the above how the message was actually presented or has a partialtranslation taken >place? Just asking because it would seem to indicate further confusion about the locale. This is an exact copy of the screen contents, no translation by me has taken place. Indeed strange, "ERROR" (english) "KONTEXT" (german, one line below) ?? To add further confusion: I have a report that the error also appeared on a non-VM native PC with Windows 7 Enterprise. Unfortunately, I dont have the exact error message, but I try to get it ASAP. Well nothing came back to me on VMware and locales, but that does not seem to be the issue if the above is correct. So: How where the Postgres instances installed? From a package? Compiled and if so with what compiler and what settings? What happens if you?: Use --inserts with pg_dump to get INSERT statements instead of a COPY and then feed to psql. This will slow the process down, so I would try with a small sample set. Do pg_dump -Fc and then use pg_restore. The above are more a way of seeing if the issue is on a particular path or is generic, than a solution. regards, Eric Svenson -- 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
[GENERAL] Help with ltree queries
Hi, I'm trying to use PostgreSQL ltree to make a basic RBAC system as it seems a sensible thing to do because of the hierarchical parsing ltree can do ! I currently have the tables below which I've simplified as follows : /*** create table app_users (user_id char(64) not null primary key); create table app_roles (role_id char(64) not null primary key); create table app_user_role_memberships( user_id char(64) not null references app_users(user_id) on update cascade on delete cascade, role_id char(64) not null references app_roles(role_id) on update cascade on delete cascade, primary key (user_id,role_id) ); create table app_permissions ( perm_id char(64) not null primary key, perm_label ltree not null); create table app_role_perm_memberships ( role_id char(64) not null references app_roles(role_id) on update cascade on delete cascade, perm_id char(64) not null references app_permissions(perm_id) on update cascade on delete cascade, primary key (role_id,perm_id) ); / The problem I have is whilst I've come up with the model above, I can't seem to come up with a clean SQL query that doesn't look like spaghetti ! Is my model correct ? Are there better ways to do this (e.g. ltree[] instead of ltree) ? How clean can you make a query to find out whether a given user has the correct permission for a resource ? Thanks all ! Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Fwd: Fwd: [GENERAL] Problem with pg_dump and decimal mark
Hi Adrian, >Is the above how the message was actually presented or has a partial translation taken >place? Just asking because it would seem to indicate further confusion about the locale. This is an exact copy of the screen contents, no translation by me has taken place. Indeed strange, "ERROR" (english) "KONTEXT" (german, one line below) ?? To add further confusion: I have a report that the error also appeared on a non-VM native PC with Windows 7 Enterprise. Unfortunately, I dont have the exact error message, but I try to get it ASAP. regards, Eric Svenson 2014-12-04 15:23 GMT+01:00 Adrian Klaver : > On 12/04/2014 02:01 AM, Eric Svenson wrote: > >> So what if you load to the VM Postgres using the psql from the host? >>> >> >> I tried that: On the host I started psql with >> psql -h 192.168.2.55 -U postgres -p 5432 my_Database < myFile.sql >> >> I get the same error >> >> ERROR: invalid input syntax for type double precision "0.2" >> KONTEXT: COPY dev_my_settings, line xxx, column xxx: "0.2" >> > > Is the above how the message was actually presented or has a partial > translation taken place? Just asking because it would seem to indicate > further confusion about the locale. > > >> ERROR: invalid input syntax for type double precision "15.776653623" >> > > Hmm, I'm at a loss. It is not a strict Windows issue as you can restore on > other Windows machines. The remaining suspect would be VMware. VMware and > locales tickles a memory, one that I cannot drag up at the moment. One of > those things where the more you think of it the further it goes away. We > will see if working on other stuff causes it to sneak up on me:) > > > >> Regards, >> Eric Svenson >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Re: [GENERAL] (never executed) in the execution plan
Thomas Kellerer wrote:
> I just encountered something like this in an execution plan:
>
> -> Hash (cost=19865.48..19865.48 rows=489 width=12) (never executed)
> Output: ly.total_count, ly.customer_id
> -> Subquery Scan on ly (cost=19864.50..19865.48 rows=489 width=12)
> (never executed)
> Output: ly.total_count, ly.customer_id
> -> HashAggregate (cost=19864.50..19864.99 rows=489 width=4)
> (never executed)
> Output: orders_1.customer_id, count(*)
> -> Seq Scan on public.orders orders_1
> (cost=0.00..19847.00 rows=3500 width=4) (never executed)
> Output: orders_1.id, orders_1.customer_id,
> orders_1.order_date, orders_1.amount, orders_1.sales_person_id
> Filter: (date_part('year'::text,
> (orders_1.order_date)::timestamp without time zone) =
> (date_part('year'::text, (('now'::cstring)::date)::timestamp without time
> zone) - 1::double precision))
>
>
> The above is only a part of the execution plan and represents a derived table
> that is outer joined to
> the main table.
>
> Postgres is correct to not execute this, because the condition in the
> sub-query will indeed not return
> any rows.
>
> I can see why the Hash Aggregate and the Hash Join nodes can be marked as
> "(never executed").
>
> But why does the Seq Scan node have the "(never executed)" as well?
>
> I can't see how Postgres could tell that the condition won't return anything
> without actually doing
> the Seq Scan (there is no index on the column order_date)
As far as I can tell, the solution is in the part of the plan that you didn't
show.
PostgreSQL never executed any of these nodes because it didn't need them.
Maybe the "hash" node is on the right hand side of a nested loop join whose
left side returned no rows?
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] (never executed) in the execution plan
I just encountered something like this in an execution plan:
-> Hash (cost=19865.48..19865.48 rows=489 width=12) (never executed)
Output: ly.total_count, ly.customer_id
-> Subquery Scan on ly (cost=19864.50..19865.48 rows=489 width=12)
(never executed)
Output: ly.total_count, ly.customer_id
-> HashAggregate (cost=19864.50..19864.99 rows=489 width=4)
(never executed)
Output: orders_1.customer_id, count(*)
-> Seq Scan on public.orders orders_1 (cost=0.00..19847.00
rows=3500 width=4) (never executed)
Output: orders_1.id, orders_1.customer_id,
orders_1.order_date, orders_1.amount, orders_1.sales_person_id
Filter: (date_part('year'::text,
(orders_1.order_date)::timestamp without time zone) = (date_part('year'::text,
(('now'::cstring)::date)::timestamp without time zone) - 1::double precision))
The above is only a part of the execution plan and represents a derived table
that is outer joined to the main table.
Postgres is correct to not execute this, because the condition in the sub-query
will indeed not return any rows.
I can see why the Hash Aggregate and the Hash Join nodes can be marked as
"(never executed").
But why does the Seq Scan node have the "(never executed)" as well?
I can't see how Postgres could tell that the condition won't return anything
without actually doing the Seq Scan (there is no index on the column order_date)
Thomas
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
