Re: [HACKERS] Fail to search in array, produced by subquery - is it a bug?

[Dmitry Fefelov]

 
 well, if you *had* to use any you could rewrite that as:

Using ANY I'll reduce number of subqueries, there are also some array
overlappings, which works fine with same subquery - i.e. wheninstead
of  = ANY . There is not full qi\uery in my first message of course.

 SELECT *
  FROM core.tag_links ctl
  WHERE (ctl.tag_id = ANY ( array (
  SELECT ct.id
FROM core.tags ct
WHERE (LOWER(ct.tag) LIKE LOWER(('search tag')::text || '%') ESCAPE
E'\\'))
));
 

Okay, thank you for a hint. 
I asked because I thought that ANY and ALL should work too - when overlap 
() works.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Fail to search in array, produced by subquery - is it a bug?

[Dmitry Fefelov]

With Postgres 8.4 query like 

SELECT *
  FROM core.tag_links ctl
  WHERE (ctl.tag_id = ANY (
  SELECT array_agg(ct.id) 
FROM core.tags ct
WHERE (LOWER(ct.tag) LIKE LOWER(('search tag')::text || '%') ESCAPE
E'\\')
));

produces error:

ERROR:  operator does not exist: bigint = bigint[]
ROW 3:  WHERE (ctl.tag_id = ANY (
 ^
HINT:  No operator matches the given name and argument type(s). You might need 
to add explicit type casts.

Bith ct.id and ctl.tag_id - of type BIGINT.

Result of 

SELECT array_agg(ct.id) 
FROM core.tags ct
WHERE (LOWER(ct.tag) LIKE LOWER(('search tag')::text || '%') ESCAPE
E'\\');

 
array_agg   
   
-
 {54426,53600,54062,187207,187642,54395,53312,51912,128621,19203,6613,54462}
(1 row)

Should ANY (...)  and ALL (...) work when ... is a subquery, returning 
single ARRAY field, or maybe  I misunderstood something?

Regards, 
Dmitry

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Partitioning/inherited tables vs FKs

[Dmitry Fefelov]

 The referential integrity triggers contain some extra magic that isn't
 easily simulatable in userland, and that is necessary to make the
 foreign key constraints airtight.  We've discussed this previously but
 I don't remember which thread it was or the details of when things
 blow up.  I think it's something like this: the parent has a tuple
 that is not referenced by any child.  Transaction 1 begins, deletes
 the parent tuple (checking that it has no children), and pauses.
 Transaction 2 begins, adds a child tuple that references the parent
 tuple (checking that the parent exists, which it does), and commits.
 Transaction 1 commits.

Will SELECT ... FOR SHARE not help?

Regargs, 
Dmitry

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Feature request - function-based deferrable uniques.

[Dmitry Fefelov]

 Sure -- use CREATE UNIQUE INDEX.
 
 alvherre=# create function singleton(int) returns int immutable language sql 
as $$ select 1 $$;
 CREATE FUNCTION
 alvherre=# create table singleton (a int);
 CREATE TABLE
 alvherre=# create unique index only_one on singleton (singleton(a));
 CREATE INDEX
 alvherre=# insert into singleton values (3);
 INSERT 0 1
 alvherre=# insert into singleton values (6);
 ERROR:  llave duplicada viola restricción de unicidad «only_one»

And it's possible to make this check deferrable? Uniqueness will be validated 
on commit or SET ALL CONSTRAINTS IMMEDIATE command?

 
 
 The reason it's not supported in table_constraint, IIUC, is that the
 standard doesn't allow it, and that syntax is standards-mandated.
 CREATE INDEX, however, is not.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Feature request - function-based deferrable uniques.

[Dmitry Fefelov]

For now Postgres able to create deferrable uniques with following syntax:

...
and table_constraint is:

[ CONSTRAINT constraint_name ]
{ UNIQUE ( column_name [, ... ] ) index_parameters |
  PRIMARY KEY ( column_name [, ... ] ) index_parameters |
  CHECK ( expression ) |
  FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, 
... ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON 
UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

So, deferrable uniques now can be based on column/columns list only. It will 
be very useful if there will be possibility to specify functions in this list. 
Is it possible? 

Regards, 
Dmitry

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Partitioning syntax

[Dmitry Fefelov]

 Here is a revised partitioning syntax patch. It implements only syntax and
 on-disk structure mentioned below:
 Table Partitioning#Syntax
   http://wiki.postgresql.org/wiki/Table_partitioning#Syntax
 Table Partitioning#On-disk structure
   http://wiki.postgresql.org/wiki/Table_partitioning#On-disk_structure

Will 9.1 partitions allow to reference partitioned tables in foreign keys? 


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Partitioning syntax

[Dmitry Fefelov]

 Here is a revised partitioning syntax patch. It implements only syntax and
 on-disk structure mentioned below:
 Table Partitioning#Syntax
   http://wiki.postgresql.org/wiki/Table_partitioning#Syntax
 Table Partitioning#On-disk structure
   http://wiki.postgresql.org/wiki/Table_partitioning#On-disk_structure

Will 9.1 partitions allow to reference partitioned tables in foreign keys?

Regards, 
Dmitry

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Partitioning syntax

[Dmitry Fefelov]

  Will 9.1 partitions allow to reference partitioned tables in foreign keys?
 
 For now, you can do something like this:
 
 http://people.planetpostgresql.org/dfetter/index.php?/archives/51-
Partitioning-Is-Such-Sweet-Sorrow.html
 
 Cheers,
 David.
 

Already did ;) But workable plain references will be useful.

Regards, 
Dmitry

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Dyamic updates of NEW with pl/pgsql

[Dmitry Fefelov]

 How can a pl/pgsql trigger change the
 values of dynamic fields in NEW record ?
 
 By dynamic I mean that the field name
 is a variable in the trigger context.

It's not possible in plpgsql, but you can write plperl function, and later use 
it in plpgsql triggers.

Regards, 
Dmitry

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers