[GENERAL] Dropping all foreign keys for a column in a table
Here is a function for removing all FKs on a column (yes, PG for some
reason allows multiple similar FKs on a column):
create or replace function remove_fk_by_table_and_column(p_table_name
varchar, p_column_name varchar) returns INTEGER as $$
declare
v_fk_name varchar := NULL;
v_fk_num_removed INTEGER := 0;
begin
FOR v_fk_name IN (SELECT ss2.conname
FROM pg_attribute af, pg_attribute a,
(SELECT conname, conrelid,confrelid,conkey[i] AS conkey,
confkey[i] AS confkey
FROM (SELECT conname, conrelid,confrelid,conkey,confkey,
generate_series(1,array_upper(conkey,1)) AS i
FROM pg_constraint WHERE contype = 'f') ss) ss2
WHERE af.attnum = confkey
AND af.attrelid = confrelid
AND a.attnum = conkey
AND a.attrelid = conrelid
AND a.attrelid = p_table_name::regclass
AND a.attname = p_column_name) LOOP
execute 'alter table ' || quote_ident(p_table_name) || ' drop
constraint ' || quote_ident(v_fk_name);
v_fk_num_removed = v_fk_num_removed + 1;
END LOOP;
return v_fk_num_removed;
end;
$$ language plpgsql;
Usage:
select remove_fk_by_table_and_column('my_table', 'some_column');
I find myself often having to remove FK-constraints on a column because
they are refactored to point to other columns or whatever, and I thought
this might be useful to others.
--
Andreas Joseph Kroghandr...@officenet.no - mob: +47 909 56 963
Senior Software Developer / CEO - OfficeNet AS - http://www.officenet.no
Public key: http://home.officenet.no/~andreak/public_key.asc
--
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] Dropping a column on parent table doesn't propagate to children?
On Wed, Aug 29, 2012 at 12:26 AM, Moshe Jacobson mo...@neadwerx.com wrote:
The docs said that the descendant tables' columns would be removed unless
they had had their own definition for that column. I'm not sure what that
It means that when you DEFINE columns in the inherited table they will
be independent from the parent table. So anything you do with the
columns in the parent table will not affect such columns in the
inherited one.
means, but the descendant tables were created using like tb_audit_event to
inherit the columns.
When LIKE table_name is specified in the definition of a table it
means that all the columns will be DEFINED in the new table. You
should not specify LIKE table_name in the definition of the inherited
tables to make the columns to be INHERITED instead of DEFINED.
Here you will find a very good explanation of the inheritance
http://www.postgresql.org/docs/9.1/interactive/ddl-inherit.html
Any idea why the descendant columns are not dropping along with the parent?
Here are the table descriptions followed by my table alter and check:
postgres@zeus=hera:ises=# \d tb_audit_event
Table public.tb_audit_event
Column |Type |
Modifiers
+-+-
audit_event| integer | not null default
nextval('sq_pk_audit_event'::regclass)
audit_field| integer | not null
row_pk_val | integer | not null
recorded | timestamp without time zone | not null default now()
entity | integer | not null
row_op | character(1)| not null
op_sequence| integer | not null
transaction_id | bigint | not null
process_id | integer | not null
old_value | text|
new_value | text|
Indexes:
tb_audit_event_pkey PRIMARY KEY, btree (audit_event)
tb_audit_event_recorded_key btree (recorded)
tb_audit_event_transaction_id_key btree (transaction_id)
Check constraints:
tb_audit_event_row_op_check CHECK (row_op = ANY (ARRAY['I'::bpchar,
'U'::bpchar, 'D'::bpchar]))
Foreign-key constraints:
tb_audit_event_audit_field_fkey FOREIGN KEY (audit_field) REFERENCES
tb_audit_field(audit_field)
tb_audit_event_entity_fkey FOREIGN KEY (entity) REFERENCES
tb_entity(entity)
Triggers:
tr_redirect_audit_events BEFORE INSERT ON tb_audit_event FOR EACH ROW
EXECUTE PROCEDURE fn_redirect_audit_events()
Number of child tables: 17 (Use \d+ to list them.)
postgres@zeus=hera:ises=# \d audit_log.tb_audit_event_20120826_0208
Table audit_log.tb_audit_event_20120826_0208
Column |Type |
Modifiers
+-+-
audit_event| integer | not null default
nextval('sq_pk_audit_event'::regclass)
audit_field| integer | not null
row_pk_val | integer | not null
recorded | timestamp without time zone | not null default now()
entity | integer | not null
row_op | character(1)| not null
op_sequence| integer | not null
transaction_id | bigint | not null
process_id | integer | not null
old_value | text|
new_value | text|
Indexes:
tb_audit_event_20120826_0208_pkey PRIMARY KEY, btree (audit_event)
tb_audit_event_20120826_0208_recorded_idx btree (recorded)
tb_audit_event_20120826_0208_transaction_id_idx btree (transaction_id)
Check constraints:
tb_audit_event_20120826_0208_recorded_check CHECK (recorded =
'2012-08-19 14:57:49.315938'::timestamp without time zone AND recorded =
'2012-08-26 14:13:04.133753'::timestamp without time zone)
tb_audit_event_row_op_check CHECK (row_op = ANY (ARRAY['I'::bpchar,
'U'::bpchar, 'D'::bpchar]))
Inherits: tb_audit_event
postgres@moshe=devmain:ises=# alter table tb_audit_event drop column
audit_event;
ALTER TABLE
postgres@moshe=devmain:ises=# \d audit_log.tb_audit_event_20120826_0208
Table audit_log.tb_audit_event_20120826_0208
Column |Type |
Modifiers
+-+-
audit_event| integer | not null default
nextval('sq_pk_audit_event'::regclass)
audit_field| integer | not null
row_pk_val | integer | not null
recorded | timestamp without time zone | not
Re: [GENERAL] order of checking the unique constraints
Can somebody tell me the order in which the Unique Constraints of a table are checked, like when an INSERT is done ? I had a similar question some time ago, with an answer by Tom lane: http://archives.postgresql.org/pgsql-general/2012-03/msg00023.php You might also find interesting that this behavior can be controlled by specifying DEFERRABLE/IMEDIATE properties on your UCs and by using SET CONSTRAINTS dirrective. http://www.postgresql.org/docs/9.1/interactive/sql-set-constraints.html -- Sergey Konoplev a database and software architect http://www.linkedin.com/in/grayhemp Jabber: gray...@gmail.com Skype: gray-hemp Phone: +79160686204 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] do the files in pg_xlog differ in master and slave?
Hi, The documentation seems to say that when setting up a standby, it is OK to rsync/copy the files from the master, but the pg_xlog directory should be excluded. Is there some important difference between the files in pg_xlog in the master and those in the slave? If so, what is it? If the pg_xlog files are copied, would it cause the timeline xx of the primary does not match recovery target timeline yy error? Why? Thanks! -- Kent Tong IT author and consultant, child educator
Re: [GENERAL] Dropping all foreign keys for a column in a table
Hi,
thanks, this will help me :)
Maybe one small hint:
You use only table name variable (p_table_name) which I assume should
contain schema name. If so then quote_ident ('aaA.bbbB') will give You
aaA.bbbB but not aaA.bbbB. This will produce error. It is better
idea, in my oppinion, to add p_schema_name variable to function parameters
or table OID as p_table_name, and then get table and schema name (fully
qualified) from casting oid to regclass:
e.g.
SELECT 'pg_class'::regclass::oid
gives me: 1259
and
SELECT 1259::regclass
gives me: pg_class
You can try this with any table and second casting will give You fully
qualified name besed on provided OID.
Regards,
Bartek
2012/8/29 Andreas Joseph Krogh andr...@officenet.no
Here is a function for removing all FKs on a column (yes, PG for some
reason allows multiple similar FKs on a column):
create or replace function remove_fk_by_table_and_column(**p_table_name
varchar, p_column_name varchar) returns INTEGER as $$
declare
v_fk_name varchar := NULL;
v_fk_num_removed INTEGER := 0;
begin
FOR v_fk_name IN (SELECT ss2.conname
FROM pg_attribute af, pg_attribute a,
(SELECT conname, conrelid,confrelid,conkey[i] AS conkey,
confkey[i] AS confkey
FROM (SELECT conname, conrelid,confrelid,conkey,**confkey,
generate_series(1,array_upper(**conkey,1)) AS i
FROM pg_constraint WHERE contype = 'f') ss) ss2
WHERE af.attnum = confkey
AND af.attrelid = confrelid
AND a.attnum = conkey
AND a.attrelid = conrelid
AND a.attrelid = p_table_name::regclass
AND a.attname = p_column_name) LOOP
execute 'alter table ' || quote_ident(p_table_name) || ' drop
constraint ' || quote_ident(v_fk_name);
v_fk_num_removed = v_fk_num_removed + 1;
END LOOP;
return v_fk_num_removed;
end;
$$ language plpgsql;
Usage:
select remove_fk_by_table_and_column(**'my_table', 'some_column');
I find myself often having to remove FK-constraints on a column because
they are refactored to point to other columns or whatever, and I thought
this might be useful to others.
--
Andreas Joseph Kroghandr...@officenet.no - mob: +47 909 56 963
Senior Software Developer / CEO - OfficeNet AS - http://www.officenet.no
Public key:
http://home.officenet.no/~**andreak/public_key.aschttp://home.officenet.no/~andreak/public_key.asc
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/**mailpref/pgsql-generalhttp://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PQfformat question and retrieving bytea data in C
I have a question regarding the return value of PQfformat()
I have a 'data' column in my table, type bytea (postgresql 9.1.5).
In postgresql.conf:
bytea_output = 'escape'
When I execute the query:
PGresult *res = PQexec(db, SELECT data::bytea FROM data_table WHERE id='xxx')
And I run through the results:
int i, j;
for (i = 0; i PQntuples(res); i++) {
for (j = 0; j PQnfields(res); j++) {
printf(Format %d: %d\n, j, PQfformat(res, j));
printf(Type %d: %d\n, j, PQftype(res, j));
}
}
This prints that the format is type 0, and the type is 17.
Shouldn't the format be 1 (binary data)?
I am getting a discrepancy between data that I put into the table and
data I retrieve.
When I dump the data, using:
int di;
char *val = PQgetvalue(res, i, j);
for (di = 0; di 16; di++) fprintf(stderr, %2x , val[di]);
I see the following:
30 5c 33 33 32 5c 30 30 30 5c 30 31 31 5c 30 30
But when I look at the same data in the database:
psql select encode(substr(data, 0, 16), 'hex') from data_table where id='xxx';
encode
30da00090132420520203137323030
This is the data I'm expecting to get back. Is the '00' (third byte)
causing the problem?
The data looks the same at a certain place (ie it starts with the same
byte 30, then the C code has 22 bytes whereas the db hex dump has 7
bytes, then the data is the same again. The 7/22 number of bytes isn't
always the same, across the different data values).
--
Jason Armstrong
--
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] PQfformat question and retrieving bytea data in C
Hey Jason,
2012/8/29 Jason Armstrong j...@riverdrums.com
I have a question regarding the return value of PQfformat()
I have a 'data' column in my table, type bytea (postgresql 9.1.5).
In postgresql.conf:
bytea_output = 'escape'
When I execute the query:
PGresult *res = PQexec(db, SELECT data::bytea FROM data_table WHERE
id='xxx')
PQexec() always returns data in the text format. You should use
PQexecParams() to obtain the data as binary.
And I run through the results:
int i, j;
for (i = 0; i PQntuples(res); i++) {
for (j = 0; j PQnfields(res); j++) {
printf(Format %d: %d\n, j, PQfformat(res, j));
printf(Type %d: %d\n, j, PQftype(res, j));
}
}
This prints that the format is type 0, and the type is 17.
Shouldn't the format be 1 (binary data)?
I am getting a discrepancy between data that I put into the table and
data I retrieve.
When I dump the data, using:
int di;
char *val = PQgetvalue(res, i, j);
for (di = 0; di 16; di++) fprintf(stderr, %2x , val[di]);
I see the following:
30 5c 33 33 32 5c 30 30 30 5c 30 31 31 5c 30 30
But when I look at the same data in the database:
psql select encode(substr(data, 0, 16), 'hex') from data_table where
id='xxx';
encode
30da00090132420520203137323030
This is the data I'm expecting to get back. Is the '00' (third byte)
causing the problem?
The data looks the same at a certain place (ie it starts with the same
byte 30, then the C code has 22 bytes whereas the db hex dump has 7
bytes, then the data is the same again. The 7/22 number of bytes isn't
always the same, across the different data values).
--
Jason Armstrong
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
// Dmitriy.
Re: [GENERAL] Views versus user-defined functions: formatting, comments, performance, etc.
On Tue, Aug 28, 2012 at 3:25 PM, Dmitriy Igrishin dmit...@gmail.com wrote:
2012/8/20 Merlin Moncure mmonc...@gmail.com
On Sun, Aug 19, 2012 at 8:14 AM, Dmitriy Igrishin dmit...@gmail.com
wrote:
For various reasons, this often goes the wrong way. Views are often
the right way to go. +1 on your comment above -- the right way to do
views (and SQL in general) is to organize scripts and to try and avoid
managing everything through GUI tools. It works.
The drawback of this approach is that in some cases we need a
factory function(s) (in terms of the OOP) which returns one or a
set of objects (i.e. the function returns the view type). But since
the views are not in the dump we are forced to abandon this solution
and go with workarounds (such as creating extra composite types
to use as returning values or use the tables).
Could you elaborate on this?
Suppose we've designed a simple class hierarchy (I'll use C++ notation):
class User { ... };
class Real_user : public User { ... };
class Pseudo_user : public User { ... };
Suppose we've decided that objects of these classes will be stored
in one database table:
CREATE TYPE user_type AS ENUM ('real', 'pseudo');
CREATE TABLE user (id serial NOT NULL,
tp user_type NOT NULL,
user_property1 text NOT NULL,
user_property2 text NOT NULL,
real_user_property1 text NULL,
real_user_property2 text NULL,
pseudo_user_property1 text NULL);
For simple mapping we've creating the (updatable, with rules) views:
CREATE VIEW real_user_view
AS SELECT * FROM user WHERE tp = 'real';
CREATE VIEW pseudo_user_view
AS SELECT * FROM user WHERE tp = 'pseudo';
CREATE VIEW user_view
AS SELECT * FROM real_user_view
UNION ALL SELECT * FROM pseudo_user_view;
The C++ classes above will operate on these views.
Finally, suppose we need a function which gets a Real_user's
instance by known identifier (or a key):
The C++ function may be defined as:
Real_user* real_user(int id);
At the same time this function can call PL/pgSQL's function:
CREATE FUNCTION real_user(id integer)
RETURNS real_user_view ...
So, the factory function real_user() is depends on the view. And
when the views are not in the dump (stored in the separate place)
this is an annoying limitation and we must use some of the
workarounds. (Use the table user as a return value or create
an extra composite type with the same structure as for the real_user_view).
Hm, couple points (and yes, this is a common problem):
*) how come you don't have your function depend on the table instead
of the view? this has the neat property of having the function
automatically track added columns to the table.
*) if that's still a headache from dependency point of view, maybe you
can use composite-type implemented table:
postgres=# create type foo as (a int, b int);
CREATE TYPE
postgres=# create table bar of foo;
CREATE TABLE
postgres=# create view baz as select * from bar;
CREATE VIEW
postgres=# alter type foo add attribute c int cascade;
ALTER TYPE
postgres=# \d bar
Table public.bar
Column | Type | Modifiers
+-+---
a | integer |
b | integer |
c | integer |
Typed table of type: foo
*) do you really need a factory function to create 'user' -- why not
allow regular inserts?
*) I usually do some variant of this:
create table fruit
(
fruit_id int primary key,
type text,
freshness numeric
);
create table apple
(
fruit_id int primary key references fruit on delete cascade
deferrable initially deferred,
cyanide_content numeric
);
create table orange
(
fruit_id int primary key references fruit on delete cascade
deferrable initially deferred,
vitamin_c_content numeric
);
create or replace function hs(r anyelement) returns hstore as
$$
select hstore($1);
$$ language sql immutable strict;
create or replace view fruit_ext as
select f.*,
coalesce(hs(a), hs(o)) as properties
from fruit f
left join apple a using(fruit_id)
left join orange o using(fruit_id);
insert into fruit values(1, 'apple', 2.0);
insert into fruit values(2, 'orange', 3.5);
insert into apple values(1, 0.3);
insert into orange values(2, 0.012);
This seems to work well especially if you have a lot of
specializations of the 'base type' and you can season deletions to
taste with appropriate RI triggers if you want. An alternate way to
do it is to include fruit.type in the primary key, forcing the
dependent fruit back to the proper record though. My main gripe about
it is that it there's no way to make sure that a 'fruit' points at the
proper dependent table based on type with a pure constraint.
Yet another way of doing this is to simple hstore the extended
properties into the base table so that everything is stuffed in one
[GENERAL] calling a C function from pgsql function
Hi All I have created a function in C language . This is to be called from a pgsql function . Let say the C language function name is test1 and pgsql function name is test . test1 is called from test three times with different arguments . For example : test () begin test1(arg1);--test1 is a C library function returning an int test1(arg2); test1(arg3); end; Now, my questions are : 1. what are the ways to get the value returned by this library function . 2. when test1 is called, will it wait for test1 to return before continuing with the next statement in test or it will continue with the next statement parallely while test1 is being executed. 3. Moreover, will test1 be called sequentially in order for all the three arguments i.e arg1 first and arg3 in last. Or there can be a different order. Thanks.. Tamanna -- Tamanna | Associate Consultant GlobalLogic Inc. | Innovation by Design ARGENTINA | CHILE | CHINA | INDIA | ISRAEL | UKRAINE | UK | USA Office: 0120-4062000 x 2971 www.globallogic.com http://www.globallogic.com/email_disclaimer.txt
Re: [GENERAL] PQfformat question and retrieving bytea data in C
On Wed, Aug 29, 2012 at 8:05 AM, Dmitriy Igrishin dmit...@gmail.com wrote: Hey Jason, 2012/8/29 Jason Armstrong j...@riverdrums.com I have a question regarding the return value of PQfformat() I have a 'data' column in my table, type bytea (postgresql 9.1.5). In postgresql.conf: bytea_output = 'escape' When I execute the query: PGresult *res = PQexec(db, SELECT data::bytea FROM data_table WHERE id='xxx') PQexec() always returns data in the text format. You should use PQexecParams() to obtain the data as binary. Also see libpqtypes. It abstracts you from the wire format and returns data in a regular way: int success; PGint4 i4; PGtext text; PGbytea bytea; PGpoint pt; PGresult *res = PQexec(conn, SELECT i,t,b,p FROM tbl); /* Get some field values from the result (order doesn't matter) */ success = PQgetf(res, 0,/* get field values from tuple 0 */ %int4 #text %bytea %point, /* type format specifiers (get text by name '#') */ 0, i4, /* get an int4 from field num 0 */ t, text, /* get a text from field name t */ 2, bytea, /* get a bytea from field num 2 */ 3, pt);/* get a point from field num 3 */ /* Output an error message using PQgeterror(3). */ if(!success) fprintf(stderr, *ERROR: %s\n, PQgeterror()); /* Output the values, do this before PQclear() */ else printf(int4=%d, text=%s, bytea=%d bytes, point=(%f,%f)\n, i4, text, bytea.len, pt.x, pt.y); PQclear(res); merlin -- 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] PQfformat question and retrieving bytea data in C
On Wed, Aug 29, 2012 at 10:30 PM, Jason Armstrong j...@riverdrums.com wrote: I see the following: 30 5c 33 33 32 5c 30 30 30 5c 30 31 31 5c 30 30 But when I look at the same data in the database: psql select encode(substr(data, 0, 16), 'hex') from data_table where id='xxx'; encode 30da00090132420520203137323030 Here's what you're seeing: 0\332\000\011\00 5c is a backslash, the rest are all digits. The backslashes introduce octal escape codes - that's what bytea_output = 'escape' means. 0332 is 0xda, 011 is 0x09, etc. You're seeing the same values come up in the cases where they don't need to be escaped, like the 0x30 at the beginning. ChrisA -- 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] calling a C function from pgsql function
On 08/29/2012 09:18 PM, tamanna madaan wrote: 1. what are the ways to get the value returned by this library function . You need to wrap the C library function with a PostgreSQL C extension that uses the fmgr.h APIs, so it's callable from SQL and can return a result to SQL. See: http://www.postgresql.org/docs/current/static/xfunc-c.html 2. when test1 is called, will it wait for test1 to return before continuing with the next statement in test or it will continue with the next statement parallely while test1 is being executed. The call will return when the C wrapper function returns, so they'll be executed in series not in parallel. 3. Moreover, will test1 be called sequentially in order for all the three arguments i.e arg1 first and arg3 in last. Or there can be a different order. The calls will be executed in the order they appear in the PL/PgSQL function. -- Craig Ringer -- 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] calling a C function from pgsql function
Hello see http://www.postgresql.org/docs/9.1/static/xfunc-c.html plpgsql can call any sql function, so you have to register your C function as custom postgresql sql function Regards Pavel Stehule 2012/8/29 tamanna madaan tamanna.mad...@globallogic.com: Hi All I have created a function in C language . This is to be called from a pgsql function . Let say the C language function name is test1 and pgsql function name is test . test1 is called from test three times with different arguments . For example : test () begin test1(arg1);--test1 is a C library function returning an int test1(arg2); test1(arg3); end; Now, my questions are : 1. what are the ways to get the value returned by this library function . 2. when test1 is called, will it wait for test1 to return before continuing with the next statement in test or it will continue with the next statement parallely while test1 is being executed. 3. Moreover, will test1 be called sequentially in order for all the three arguments i.e arg1 first and arg3 in last. Or there can be a different order. Thanks.. Tamanna -- Tamanna | Associate Consultant GlobalLogic Inc. | Innovation by Design ARGENTINA | CHILE | CHINA | INDIA | ISRAEL | UKRAINE | UK | USA Office: 0120-4062000 x 2971 www.globallogic.com http://www.globallogic.com/email_disclaimer.txt -- 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] Dropping a column on parent table doesn't propagate to children?
Thanks Sergey.
I create these inherited tables from the main table, and then move them
into a different schema that is backed up separately from the main schema,
since they are for audit logging, which gets very big.
Questions:
1. If I want the inherited table's columns indexed the same way as the
parent, must I create new indexes on the inherited table?
2. If I move the inherited table to a new schema, will its indexes also be
moved into the new schema?
3. Any difference in behavior regarding check constraints, schemas,
indexes, etc that I should be aware of between inherited tables created
with pure inheritance as opposed to like parent_table?
Thank you.
On Wed, Aug 29, 2012 at 4:35 AM, Sergey Konoplev
sergey.konop...@postgresql-consulting.com wrote:
On Wed, Aug 29, 2012 at 12:26 AM, Moshe Jacobson mo...@neadwerx.com
wrote:
The docs said that the descendant tables' columns would be removed unless
they had had their own definition for that column. I'm not sure what that
It means that when you DEFINE columns in the inherited table they will
be independent from the parent table. So anything you do with the
columns in the parent table will not affect such columns in the
inherited one.
means, but the descendant tables were created using like
tb_audit_event to
inherit the columns.
When LIKE table_name is specified in the definition of a table it
means that all the columns will be DEFINED in the new table. You
should not specify LIKE table_name in the definition of the inherited
tables to make the columns to be INHERITED instead of DEFINED.
Here you will find a very good explanation of the inheritance
http://www.postgresql.org/docs/9.1/interactive/ddl-inherit.html
Any idea why the descendant columns are not dropping along with the
parent?
Here are the table descriptions followed by my table alter and check:
postgres@zeus=hera:ises=# \d tb_audit_event
Table public.tb_audit_event
Column |Type |
Modifiers
+-+-
audit_event| integer | not null default
nextval('sq_pk_audit_event'::regclass)
audit_field| integer | not null
row_pk_val | integer | not null
recorded | timestamp without time zone | not null default now()
entity | integer | not null
row_op | character(1)| not null
op_sequence| integer | not null
transaction_id | bigint | not null
process_id | integer | not null
old_value | text|
new_value | text|
Indexes:
tb_audit_event_pkey PRIMARY KEY, btree (audit_event)
tb_audit_event_recorded_key btree (recorded)
tb_audit_event_transaction_id_key btree (transaction_id)
Check constraints:
tb_audit_event_row_op_check CHECK (row_op = ANY (ARRAY['I'::bpchar,
'U'::bpchar, 'D'::bpchar]))
Foreign-key constraints:
tb_audit_event_audit_field_fkey FOREIGN KEY (audit_field)
REFERENCES
tb_audit_field(audit_field)
tb_audit_event_entity_fkey FOREIGN KEY (entity) REFERENCES
tb_entity(entity)
Triggers:
tr_redirect_audit_events BEFORE INSERT ON tb_audit_event FOR EACH ROW
EXECUTE PROCEDURE fn_redirect_audit_events()
Number of child tables: 17 (Use \d+ to list them.)
postgres@zeus=hera:ises=# \d audit_log.tb_audit_event_20120826_0208
Table
audit_log.tb_audit_event_20120826_0208
Column |Type |
Modifiers
+-+-
audit_event| integer | not null default
nextval('sq_pk_audit_event'::regclass)
audit_field| integer | not null
row_pk_val | integer | not null
recorded | timestamp without time zone | not null default now()
entity | integer | not null
row_op | character(1)| not null
op_sequence| integer | not null
transaction_id | bigint | not null
process_id | integer | not null
old_value | text|
new_value | text|
Indexes:
tb_audit_event_20120826_0208_pkey PRIMARY KEY, btree (audit_event)
tb_audit_event_20120826_0208_recorded_idx btree (recorded)
tb_audit_event_20120826_0208_transaction_id_idx btree
(transaction_id)
Check constraints:
tb_audit_event_20120826_0208_recorded_check CHECK (recorded =
'2012-08-19 14:57:49.315938'::timestamp without time zone AND recorded =
'2012-08-26
Re: [GENERAL] Views versus user-defined functions: formatting, comments, performance, etc.
On Wed, Aug 29, 2012 at 6:15 AM, Merlin Moncure mmonc...@gmail.com wrote: Hm, couple points (and yes, this is a common problem): *) how come you don't have your function depend on the table instead of the view? this has the neat property of having the function automatically track added columns to the table. Plus there are lots of really cool things about function/table dependencies. *) if that's still a headache from dependency point of view, maybe you can use composite-type implemented table: postgres=# create type foo as (a int, b int); CREATE TYPE postgres=# create table bar of foo; CREATE TABLE That's a useful syntax there. *) do you really need a factory function to create 'user' -- why not allow regular inserts? I don't know about his case but with LedgerSMB our new code all maps inserts into stored procedures. These have a useful property of discoverability although I suppose an insert would too.However if you are going the insert route, it may be good to separate physical from logical storage, which may be where the views and functions come in. Also showing before where table inheritance can make this a bit better and how to solve your key issue (which again inheritance provides a solution for, properly used). *) I usually do some variant of this: create table fruit ( fruit_id int primary key, type text, freshness numeric ); I would suggest adding fruit.type to the primary key. From here there are all sorts of things you can do and depending on number of types, inheritance can be a net win (see my upcoming post tomorrow on this). Also create another table: CREATE TABLE fruit_ref ( fruit_id int, fruit_type text ); This is then useful for creating inherited interfaces. More on this below. create table apple ( fruit_id int primary key references fruit on delete cascade deferrable initially deferred, cyanide_content numeric ); Change this to: CREATE TABLE apple ( cyanide_content numeric, primary key (fruit_id, fruit_type) check (fruit_type = 'apple'), foreign key (fruit_id, fruit_type) references fruit (fruit_id, type) deferrable initially immediate, ) inherits (fruit_ref); Make similar changes to other tables below create table orange ( fruit_id int primary key references fruit on delete cascade deferrable initially deferred, vitamin_c_content numeric ); Now, you can also do as follows: CREATE OR REPLACE FUNCTION fruit(fruit_ref) returns fruit language sql as $$ select * from fruit where fruit_id = $1.fruit_id; $$; This way you technically can do something like: select (a.fruit).* from apple a; although that will essentially force a nested loop join, and probably even worse than that so keep those to the minimum. But now we can decide on how to enforce the type constraint. The simplest way if you don't have too many subtypes is probably to add the following columns to fruit: ALTER TABLE fruit ADD apple_id int; ALTER TABLE fruit ADD FOREIGN KEY (apple_id, type) REFERENCES apple (fruit_id, type) DEFERRABLE INITIALLY DEFERRED; And then do the same for orange etc. you can then: ALTER TABLE fruit ADD CHECK ((type = 'apple' and apple_id IS NOT NULL) OR (type = 'orange' AND orange_id IS NOT NULL) etc ; This way you only get a fairly complicated set of type constraints, and the interface to fruit is in fact guaranteed to be unique and enforced. The problem though is that if you have a very large number of subtypes, this becomes sufficiently complex that constraint triggers against the fruit_ref inheritance tree may become a net win over individual foreign keys. Inheritance also simplifies adding sub-types because you inherit the subtype interface and then work from there for joins and doesn't have to be used to enforce keys to get there however. This seems to work well especially if you have a lot of specializations of the 'base type' and you can season deletions to taste with appropriate RI triggers if you want. An alternate way to do it is to include fruit.type in the primary key, forcing the dependent fruit back to the proper record though. My main gripe about it is that it there's no way to make sure that a 'fruit' points at the proper dependent table based on type with a pure constraint. deferred foreign key constraints can do that if you add one key to fruit for each base table. Yet another way of doing this is to simple hstore the extended properties into the base table so that everything is stuffed in one table -- that discards all type safety though. I'm curious about what others have come up with in terms of solving this problem. Aside: a better way of doing this is the problem that table inheritance was trying to solve (and didn't). Inheritance certainly hasn't become hassle-free in terms of this sort of modelling and in fact is usually on the balance not a net gain. It may be helpful in defining interfaces
[GENERAL] Problem with initdb and ephemeral drives when rebooting
Hello, When doing the setup for a benchmark of pgsql on an High IO instance of Amazon, I got the following problem and was wondering if it is expected: On FreeBSD 9.0 amd64, I installed PostgreSQL 9.1.5 on the boot drive (UFS), created a ZFS pool using the 2 SSD drives (tank/db), chown pgsql tank/db, ran initdb -D /tank/db as pgsql and all went well. When I rebooted the instance, I tried to recreate the db in the same way, but I get the following error: FATAL: could not open file pg_xlog/00010001 I searched a bit to find what could cause this, but did not find any useful information. Sébastien
[GENERAL][postgis-users] pg_dump -s should use add_geometrycolumn(...)
Hi, pg_dump -s should use add_geometrycolumn(...) instead of creating a column+constraints with normal DDL Because, when you don't dump the data, then the record in geometry_columns is lost. Cheers, WBL -- Quality comes from focus and clarity of purpose -- Mark Shuttleworth
Re: [GENERAL] Understanding autocommit
On Wed, Aug 22, 2012 at 6:52 PM, Albe Laurenz laurenz.a...@wien.gv.at wrote: Chris Angelico wrote: I'm looking at these two pages: http://www.postgresql.org/docs/9.1/static/ecpg-sql-set-autocommit.html http://www.postgresql.org/docs/9.1/static/sql-start-transaction.html I'm sure there's something really obvious here, but... how do I find out whether my program's running in autocommit mode or not? An embedded SQL program is a program written in C that makes use of ecpg. The server itself has autocommit, so every program that uses PostgreSQL will be in autocommit mode by default. The only way around that are explicit BEGIN or START TRANSACTION commands (which is what ecpg uses). Thanks Albe. I've poked around a bit more with this question, and it seems that the functionality was moved out of the server and into the client. It's no longer possible to have autocommit set to anything other than true. (But in case anyone else is wondering, the query show autocommit is what I was looking for, to find out what current status is.) So I guess now I need to check out the PHP bindings, since all my C++ code uses libpqxx and much better handling of transactions. (And I rather doubt that you guys will know what Pike is, much less whether it can automatically open a transaction and not commit it when a query comes in.) I'd really rather get off PHP altogether, but that isn't looking likely at the moment! ChrisA -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] using vars in ddl in procedure call
Hi: v9.0.1 on linux. Trying (failing) a test to see if I can run ddl in a procedure where elements of the ddl are vars. Consider... create or replace function newcol (text) returns integer as $$ declare newcol alias for $1; begin alter table target add column newcol text; return(0); end; $$ language plpgsql; This successfully adds a column called newcol instead of what I passed it as an arg. Any suggestions on how to make this work? Thanks !
Re: [GENERAL] String comparision in PostgreSQL
On Tue, Aug 28, 2012 at 9:46 AM, Nicola Cisternino ncis...@tiscali.it wrote: Hi all, I'm valutating a complex porting of our application based on Sybase SqlAnywhere on PostgreSQL (I've love it ...) and I'd like to have your opinion about searching/ordering funcionality. The problem is about string comparision. MS Sql server, MySql, SqlAnywhere and other DB engine allow a simple definition of case sensitive/insensitive behavior using char, varchar and text field type. In PostgreSQL I've already tried to use citext, lower() function (applied to indexes, too ...), ILIKE an so on . but nothing really work as I need (poor performances ...) !! hm, poor performance? can you elaborate? merlin -- 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] using vars in ddl in procedure call
Hello, you need to use execute command i.e. execute 'alter table ' || newcol || ';' Regards From: Gauthier, Dave dave.gauth...@intel.com To: pgsql-general@postgresql.org pgsql-general@postgresql.org Sent: Wednesday, August 29, 2012 4:56 PM Subject: [GENERAL] using vars in ddl in procedure call Hi: v9.0.1 on linux. Trying (failing) a test to see if I can run ddl in a procedure where elements of the ddl are vars. Consider... create or replace function newcol (text) returns integer as $$ declare newcol alias for $1; begin alter table target add column newcol text; return(0); end; $$ language plpgsql; This successfully adds a column called newcol instead of what I passed it as an arg. Any suggestions on how to make this work? Thanks !
[GENERAL] String comparision in PostgreSQL
Hi all, I'm valutating a complex porting of our application based on Sybase SqlAnywhere on PostgreSQL (I've love it ...) and I'd like to have your opinion about searching/ordering funcionality. The problem is about string comparision. MS Sql server, MySql, SqlAnywhere and other DB engine allow a simple definition of case sensitive/insensitive behavior using char, varchar and text field type. In PostgreSQL I've already tried to use citext, lower() function (applied to indexes, too ...), ILIKE an so on . but nothing really work as I need (poor performances ...) !! My questions are: 1) Why PostgreSQL don't use COLLATE to manage case sensitive / insensitive comparision (I think it's the best and ANSI standard way ) ? 2) Can I build a custom COLLATION (for example named: NOCASE ) to apply to my DB objects ? What's the way (... some example ? ) ??? Thanks. Best Regards. Nicola.
Re: [GENERAL] using vars in ddl in procedure call
Gauthier, Dave dave.gauth...@intel.com writes: Trying (failing) a test to see if I can run ddl in a procedure where elements of the ddl are vars. Consider... You'll need to construct the command as a string value and then run it with EXECUTE. Beware of quoting issues (quote_ident and quote_literal are your friends). 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][postgis-users] pg_dump -s should use add_geometrycolumn(...)
On Wednesday, August 29, 2012 04:44:14 PM Willy-Bas Loos wrote: Hi, pg_dump -s should use add_geometrycolumn(...) instead of creating a column+constraints with normal DDL Because, when you don't dump the data, then the record in geometry_columns is lost. That shouldn't be a problem with postgres 2 anymore as far as I understand things? Greetings, Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Views versus user-defined functions: formatting, comments, performance, etc.
2012/8/29 Merlin Moncure mmonc...@gmail.com
On Tue, Aug 28, 2012 at 3:25 PM, Dmitriy Igrishin dmit...@gmail.com
wrote:
2012/8/20 Merlin Moncure mmonc...@gmail.com
On Sun, Aug 19, 2012 at 8:14 AM, Dmitriy Igrishin dmit...@gmail.com
wrote:
For various reasons, this often goes the wrong way. Views are often
the right way to go. +1 on your comment above -- the right way to do
views (and SQL in general) is to organize scripts and to try and
avoid
managing everything through GUI tools. It works.
The drawback of this approach is that in some cases we need a
factory function(s) (in terms of the OOP) which returns one or a
set of objects (i.e. the function returns the view type). But since
the views are not in the dump we are forced to abandon this solution
and go with workarounds (such as creating extra composite types
to use as returning values or use the tables).
Could you elaborate on this?
Suppose we've designed a simple class hierarchy (I'll use C++ notation):
class User { ... };
class Real_user : public User { ... };
class Pseudo_user : public User { ... };
Suppose we've decided that objects of these classes will be stored
in one database table:
CREATE TYPE user_type AS ENUM ('real', 'pseudo');
CREATE TABLE user (id serial NOT NULL,
tp user_type NOT NULL,
user_property1 text NOT NULL,
user_property2 text NOT NULL,
real_user_property1 text NULL,
real_user_property2 text NULL,
pseudo_user_property1 text NULL);
For simple mapping we've creating the (updatable, with rules) views:
CREATE VIEW real_user_view
AS SELECT * FROM user WHERE tp = 'real';
CREATE VIEW pseudo_user_view
AS SELECT * FROM user WHERE tp = 'pseudo';
CREATE VIEW user_view
AS SELECT * FROM real_user_view
UNION ALL SELECT * FROM pseudo_user_view;
The C++ classes above will operate on these views.
Finally, suppose we need a function which gets a Real_user's
instance by known identifier (or a key):
The C++ function may be defined as:
Real_user* real_user(int id);
At the same time this function can call PL/pgSQL's function:
CREATE FUNCTION real_user(id integer)
RETURNS real_user_view ...
So, the factory function real_user() is depends on the view. And
when the views are not in the dump (stored in the separate place)
this is an annoying limitation and we must use some of the
workarounds. (Use the table user as a return value or create
an extra composite type with the same structure as for the
real_user_view).
Hm, couple points (and yes, this is a common problem):
*) how come you don't have your function depend on the table instead
of the view? this has the neat property of having the function
I always do emphasis on the code style and on the easiness of
maintenance. And I looks at the views as on the classes (aka abstractions).
In many cases I don't want to care how (and where) the data is actually
stored -- in the one table, or in the many tables, or whatever.
AFAIK, the main goal of the views to provide such abstraction.
automatically track added columns to the table.
Agreed, this is a nice feature.
*) if that's still a headache from dependency point of view, maybe you
can use composite-type implemented table:
postgres=# create type foo as (a int, b int);
CREATE TYPE
postgres=# create table bar of foo;
CREATE TABLE
postgres=# create view baz as select * from bar;
CREATE VIEW
postgres=# alter type foo add attribute c int cascade;
ALTER TYPE
postgres=# \d bar
Table public.bar
Column | Type | Modifiers
+-+---
a | integer |
b | integer |
c | integer |
Typed table of type: foo
Thanks for the solution! But it seems like a workaround here.
*) do you really need a factory function to create 'user' -- why not
allow regular inserts?
By factory function I mean the function which creates an instance
for the client -- i.e. selecting object from the data source :-)
*) I usually do some variant of this:
create table fruit
(
fruit_id int primary key,
type text,
freshness numeric
);
create table apple
(
fruit_id int primary key references fruit on delete cascade
deferrable initially deferred,
cyanide_content numeric
);
create table orange
(
fruit_id int primary key references fruit on delete cascade
deferrable initially deferred,
vitamin_c_content numeric
);
create or replace function hs(r anyelement) returns hstore as
$$
select hstore($1);
$$ language sql immutable strict;
create or replace view fruit_ext as
select f.*,
coalesce(hs(a), hs(o)) as properties
from fruit f
left join apple a using(fruit_id)
left join orange o using(fruit_id);
insert into fruit
Re: [GENERAL][postgis-users] pg_dump -s should use add_geometrycolumn(...)
On Wed, Aug 29, 2012 at 5:23 PM, Andres Freund and...@2ndquadrant.comwrote: That shouldn't be a problem with postgres 2 anymore as far as I understand things? Why? -- Quality comes from focus and clarity of purpose -- Mark Shuttleworth
Re: [GENERAL][postgis-users] pg_dump -s should use add_geometrycolumn(...)
On Wednesday, August 29, 2012 05:41:07 PM Willy-Bas Loos wrote: On Wed, Aug 29, 2012 at 5:23 PM, Andres Freund and...@2ndquadrant.comwrote: That shouldn't be a problem with postgres 2 anymore as far as I understand things? Argh, postgis 2. Two things: * the geometry_columns table is not a table anymore but a view of the postgres catalogs * extensions (9.1+, used by postgis 2 onwards) can declare configuration tables now, so even if you would still need a configuration table it would get dumped in a schema only dump Also, adding postgis support into pg_dump would have been problematic given that pg_dump is part of core postgres and postgis isnt... Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] String comparision in PostgreSQL
Il 29/08/2012 17.08, Merlin Moncure ha scritto: On Tue, Aug 28, 2012 at 9:46 AM, Nicola Cisterninoncis...@tiscali.it wrote: Hi all, I'm valutating a complex porting of our application based on Sybase SqlAnywhere on PostgreSQL (I've love it ...) and I'd like to have your opinion about searching/ordering funcionality. The problem is about string comparision. MS Sql server, MySql, SqlAnywhere and other DB engine allow a simple definition of case sensitive/insensitive behavior using char, varchar and text field type. In PostgreSQL I've already tried to use citext, lower() function (applied to indexes, too ...), ILIKE an so on . but nothing really work as I need (poor performances ...) !! hm, poor performance? can you elaborate? merlin The same query using LIKE value is completed in 15 ms while using ILIKE value the execution time is 453 ms
Re: [GENERAL][postgis-users] pg_dump -s should use add_geometrycolumn(...)
Andres Freund and...@2ndquadrant.com writes: On Wednesday, August 29, 2012 04:44:14 PM Willy-Bas Loos wrote: pg_dump -s should use add_geometrycolumn(...) instead of creating a column+constraints with normal DDL Because, when you don't dump the data, then the record in geometry_columns is lost. That shouldn't be a problem with postgres 2 anymore as far as I understand things? The big picture here is that pg_dump doesn't, and is not likely ever to, know about anything as extension-specific as add_geometrycolumn(). Rather, if postgis needs some info in a special table geometry_columns, that should be handled by pg_dump dumping and restoring that table too (since, again, there is no reason for pg_dump to treat such a table specially). Willy hasn't provided enough context for us to know why that approach might not be working for him, though a first guess is that he tried to do a selective dump excluding geometry_columns. I believe there was some discussion awhile back about whether postgis could dodge this problem by cramming its additional info into type modifiers (with the geometry-type typmodin and typmodout functions becoming responsible for accessing the special table). I don't know if that can fly on syntax grounds, but if it can, I think all the core infrastructure for it is in place as of 9.1. 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] using vars in ddl in procedure call
Yup, works like a charm. Thanks Salah and Tom for the advise ! -dave -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Wednesday, August 29, 2012 11:18 AM To: Gauthier, Dave Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] using vars in ddl in procedure call Gauthier, Dave dave.gauth...@intel.com writes: Trying (failing) a test to see if I can run ddl in a procedure where elements of the ddl are vars. Consider... You'll need to construct the command as a string value and then run it with EXECUTE. Beware of quoting issues (quote_ident and quote_literal are your friends). 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][postgis-users] pg_dump -s should use add_geometrycolumn(...)
On Wednesday, August 29, 2012 06:02:24 PM Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: On Wednesday, August 29, 2012 04:44:14 PM Willy-Bas Loos wrote: pg_dump -s should use add_geometrycolumn(...) instead of creating a column+constraints with normal DDL Because, when you don't dump the data, then the record in geometry_columns is lost. That shouldn't be a problem with postgres 2 anymore as far as I understand things? The big picture here is that pg_dump doesn't, and is not likely ever to, know about anything as extension-specific as add_geometrycolumn(). Rather, if postgis needs some info in a special table geometry_columns, that should be handled by pg_dump dumping and restoring that table too (since, again, there is no reason for pg_dump to treat such a table specially). Willy hasn't provided enough context for us to know why that approach might not be working for him, though a first guess is that he tried to do a selective dump excluding geometry_columns. I believe there was some discussion awhile back about whether postgis could dodge this problem by cramming its additional info into type modifiers (with the geometry-type typmodin and typmodout functions becoming responsible for accessing the special table). I don't know if that can fly on syntax grounds, but if it can, I think all the core infrastructure for it is in place as of 9.1. As far as I know, and thats what I tried to refer to, that's done as of postgis 2.0. Yep: PostgreSQL typmod integration, for an automagical geometry_columns table Greetings, Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Dropping a column on parent table doesn't propagate to children?
Le mercredi 29 août 2012 à 09:48 -0400, Moshe Jacobson a écrit : Questions: 1. If I want the inherited table's columns indexed the same way as the parent, must I create new indexes on the inherited table? 2. If I move the inherited table to a new schema, will its indexes also be moved into the new schema? 3. Any difference in behavior regarding check constraints, schemas, indexes, etc that I should be aware of between inherited tables created with pure inheritance as opposed to like parent_table? The 'LIKE' section of the documentation for CREATE TABLE explains it : http://www.postgresql.org/docs/9.1/static/sql-createtable.html -- Vincent Veyron http://marica.fr/ Gestion informatique des sinistres d'assurances et des dossiers contentieux pour le service juridique -- 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] String comparision in PostgreSQL
On Thu, Aug 30, 2012 at 1:56 AM, Nicola Cisternino ncis...@tiscali.it wrote: The same query using LIKE value is completed in 15 ms while using ILIKE value the execution time is 453 ms Sounds to me like (pun not intended) there's an index that's being used in one case and not in the other. But taking this back a step: Do you really need case-insensitive comparisons? They become pretty much impossible once you start looking at internationalization. Sure, it's easy in ASCII. You just mask off one bit and off you go. But truly case insensitive matching gets really hairy. Can you redo things with case sensitive searches, possibly with some forcing of case in simple situations? For instance, you accept a name prefix from the user, look at it and find that it's all ASCII; lower-case it, then upper-case the first letter, then add a percent sign, and use a case-sensitive LIKE. That's going to produce correct results in most cases, and is way faster than truly case insensitive searching. ChrisA -- 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] String comparision in PostgreSQL
On Wed, Aug 29, 2012 at 10:56 AM, Nicola Cisternino ncis...@tiscali.it wrote: Il 29/08/2012 17.08, Merlin Moncure ha scritto: On Tue, Aug 28, 2012 at 9:46 AM, Nicola Cisternino ncis...@tiscali.it wrote: Hi all, I'm valutating a complex porting of our application based on Sybase SqlAnywhere on PostgreSQL (I've love it ...) and I'd like to have your opinion about searching/ordering funcionality. The problem is about string comparision. MS Sql server, MySql, SqlAnywhere and other DB engine allow a simple definition of case sensitive/insensitive behavior using char, varchar and text field type. In PostgreSQL I've already tried to use citext, lower() function (applied to indexes, too ...), ILIKE an so on . but nothing really work as I need (poor performances ...) !! hm, poor performance? can you elaborate? merlin The same query using LIKE value is completed in 15 ms while using ILIKE value the execution time is 453 ms citext unfortunately doesn't allow for index optimization of LIKE queries, which IMNSHO defeats the whole purpose. to the best way remains to use lower() create table foo(f text); create index on foo(lower(f)); select * from f where lower(f) = 'abc%' this will be index optimized and fast as long as you specified C locale for your database. merlin -- 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] Dropping a column on parent table doesn't propagate to children?
On Wed, Aug 29, 2012 at 12:11 PM, Vincent Veyron vv.li...@wanadoo.frwrote: Le mercredi 29 août 2012 à 09:48 -0400, Moshe Jacobson a écrit : Questions: 1. If I want the inherited table's columns indexed the same way as the parent, must I create new indexes on the inherited table? 2. If I move the inherited table to a new schema, will its indexes also be moved into the new schema? 3. Any difference in behavior regarding check constraints, schemas, indexes, etc that I should be aware of between inherited tables created with pure inheritance as opposed to like parent_table? The 'LIKE' section of the documentation for CREATE TABLE explains it : http://www.postgresql.org/docs/9.1/static/sql-createtable.html I don't see the answers to #1 and #2 there... -- Moshe Jacobson Nead Werx, Inc. | Senior Systems Engineer 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com
[GENERAL] C locale versus en_US.UTF8. (Was: String comparision in PostgreSQL)
On Wed, Aug 29, 2012 at 9:45 AM, Merlin Moncure mmonc...@gmail.com wrote: citext unfortunately doesn't allow for index optimization of LIKE queries, which IMNSHO defeats the whole purpose. to the best way remains to use lower() ... this will be index optimized and fast as long as you specified C locale for your database. What is the difference between C and en_US.UTF8, please? We see that the same query (that invokes a sort) runs 15% faster under the C locale. The output between C and en_US.UTF8 is identical. We're considering moving our database from en_US.UTF8 to C, but we do deal with internationalized text. Best, Aleksey -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] psql unix env variables
Is there a method for having unix env variables incorporated into a psql sql statement? Ie Export var='dev' Psql =c 'select count(*) from $var.customer;' Doug Little Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz Worldwide 500 W. Madison, Suite 1000 Chicago IL 60661| Office 312.260.2588 | Fax 312.894.5164 | Cell 847-997-5741 douglas.lit...@orbitz.commailto:douglas.lit...@orbitz.com [cid:image001.jpg@01CD85E2.C7732E50] orbitz.comhttp://www.orbitz.com/ | ebookers.comhttp://www.ebookers.com/ | hotelclub.comhttp://www.hotelclub.com/ | cheaptickets.comhttp://www.cheaptickets.com/ | ratestogo.comhttp://www.ratestogo.com/ | asiahotels.comhttp://www.asiahotels.com/ inline: image001.jpg
Re: [GENERAL] psql unix env variables
On Wednesday, August 29, 2012 12:35:32 PM Little, Douglas wrote: Is there a method for having unix env variables incorporated into a psql sql statement? Ie Export var='dev' Psql =c 'select count(*) from $var.customer;' Use double-quotes, not single-quotes. Bash won't interpolate variables into single-quoted strings. -- 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] C locale versus en_US.UTF8. (Was: String comparision in PostgreSQL)
On Wed, Aug 29, 2012 at 10:31:21AM -0700, Aleksey Tsalolikhin wrote: On Wed, Aug 29, 2012 at 9:45 AM, Merlin Moncure mmonc...@gmail.com wrote: citext unfortunately doesn't allow for index optimization of LIKE queries, which IMNSHO defeats the whole purpose. to the best way remains to use lower() ... this will be index optimized and fast as long as you specified C locale for your database. What is the difference between C and en_US.UTF8, please? We see that the same query (that invokes a sort) runs 15% faster under the C locale. The output between C and en_US.UTF8 is identical. We're considering moving our database from en_US.UTF8 to C, but we do deal with internationalized text. Well, C has reduced overhead for string comparisons, but obviously doesn't work well for international characters. The single-byte encodings have somewhat less overhead than UTF8. You can try using C locales for databases that don't require non-ASCII characters. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] psql unix env variables
On Wed, Aug 29, 2012 at 12:35:32PM -0500, Little, Douglas wrote: Is there a method for having unix env variables incorporated into a psql sql statement? Ie Export var='dev' Psql =c 'select count(*) from $var.customer;' export FOO=bar psql -c select count(*) from $FOO.customer; Note the double quotes. That allows your shell to interpolate the string into your query. Note that the interpolation is done by your shell, and not psql. Doug Little Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz Worldwide 500 W. Madison, Suite 1000 Chicago IL 60661| Office 312.260.2588 | Fax 312.894.5164 | Cell 847-997-5741 douglas.lit...@orbitz.commailto:douglas.lit...@orbitz.com [cid:image001.jpg@01CD85E2.C7732E50] orbitz.comhttp://www.orbitz.com/ | ebookers.comhttp://www.ebookers.com/ | hotelclub.comhttp://www.hotelclub.com/ | cheaptickets.comhttp://www.cheaptickets.com/ | ratestogo.comhttp://www.ratestogo.com/ | asiahotels.comhttp://www.asiahotels.com/ -Ryan Kelly -- 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] C locale versus en_US.UTF8. (Was: String comparision in PostgreSQL)
On Wed, Aug 29, 2012 at 12:43 PM, Bruce Momjian br...@momjian.us wrote: On Wed, Aug 29, 2012 at 10:31:21AM -0700, Aleksey Tsalolikhin wrote: On Wed, Aug 29, 2012 at 9:45 AM, Merlin Moncure mmonc...@gmail.com wrote: citext unfortunately doesn't allow for index optimization of LIKE queries, which IMNSHO defeats the whole purpose. to the best way remains to use lower() ... this will be index optimized and fast as long as you specified C locale for your database. What is the difference between C and en_US.UTF8, please? We see that the same query (that invokes a sort) runs 15% faster under the C locale. The output between C and en_US.UTF8 is identical. We're considering moving our database from en_US.UTF8 to C, but we do deal with internationalized text. Well, C has reduced overhead for string comparisons, but obviously doesn't work well for international characters. The single-byte encodings have somewhat less overhead than UTF8. You can try using C locales for databases that don't require non-ASCII characters. To add: The middle ground I usually choose is to have a database encoding of UTF8 but with the C (aka POSIX) locale. This gives you the ability to store any unicode but indexing operations will use the faster C string comparison operations for a significant performance boost -- especially for partial string searches on an indexed column. This is an even more attractive option in 9.1 with the ability to specify specific collations at runtime. merlin -- 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] C locale versus en_US.UTF8. (Was: String comparision in PostgreSQL)
On Wed, Aug 29, 2012 at 11:43 AM, Bruce Momjian br...@momjian.us wrote: On Wed, Aug 29, 2012 at 10:31:21AM -0700, Aleksey Tsalolikhin wrote: On Wed, Aug 29, 2012 at 9:45 AM, Merlin Moncure mmonc...@gmail.com wrote: citext unfortunately doesn't allow for index optimization of LIKE queries, which IMNSHO defeats the whole purpose. to the best way remains to use lower() ... this will be index optimized and fast as long as you specified C locale for your database. What is the difference between C and en_US.UTF8, please? We see that the same query (that invokes a sort) runs 15% faster under the C locale. The output between C and en_US.UTF8 is identical. We're considering moving our database from en_US.UTF8 to C, but we do deal with internationalized text. Well, C has reduced overhead for string comparisons, but obviously doesn't work well for international characters. The single-byte encodings have somewhat less overhead than UTF8. You can try using C locales for databases that don't require non-ASCII characters. I think you're confusing encodings with locales. C is a locale. You can have a database with a locale of C and UTF-8 encoding. create database clocale_utf8 encoding='UTF8' LC_COLLATE= 'C' template=template0; \l Name | Owner | Encoding | Collate |Ctype| Access privileges --+--+---+-+-+--- clocale_utf8 | smarlowe | UTF8 | C | en_US.UTF-8 | SQL_ASCII is the encoding equivalent of C locale, but it also allows multi-byte characters. -- 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] Dropping a column on parent table doesn't propagate to children?
Le mercredi 29 août 2012 à 13:05 -0400, Moshe Jacobson a écrit : On Wed, Aug 29, 2012 at 12:11 PM, Vincent Veyron vv.li...@wanadoo.fr wrote: Le mercredi 29 août 2012 à 09:48 -0400, Moshe Jacobson a écrit : Questions: 1. If I want the inherited table's columns indexed the same way as the parent, must I create new indexes on the inherited table? 2. If I move the inherited table to a new schema, will its indexes also be moved into the new schema? 3. Any difference in behavior regarding check constraints, schemas, indexes, etc that I should be aware of between inherited tables created with pure inheritance as opposed to like parent_table? The 'LIKE' section of the documentation for CREATE TABLE explains it : http://www.postgresql.org/docs/9.1/static/sql-createtable.html I don't see the answers to #1 and #2 there... #1 Quote : Any indexes on the original table will not be created on the new table, unless the INCLUDING INDEXES clause is specified. #2 I don't use schemas personnally, but if you do, I suggest you test and see what happens -- Moshe Jacobson Nead Werx, Inc. | Senior Systems Engineer 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com -- Vincent Veyron http://marica.fr/ Gestion informatique des sinistres d'assurances et des dossiers contentieux pour le service juridique -- 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] C locale versus en_US.UTF8. (Was: String comparision in PostgreSQL)
2012/8/29 Merlin Moncure mmonc...@gmail.com On Wed, Aug 29, 2012 at 12:43 PM, Bruce Momjian br...@momjian.us wrote: On Wed, Aug 29, 2012 at 10:31:21AM -0700, Aleksey Tsalolikhin wrote: On Wed, Aug 29, 2012 at 9:45 AM, Merlin Moncure mmonc...@gmail.com wrote: citext unfortunately doesn't allow for index optimization of LIKE queries, which IMNSHO defeats the whole purpose. to the best way remains to use lower() ... this will be index optimized and fast as long as you specified C locale for your database. What is the difference between C and en_US.UTF8, please? We see that the same query (that invokes a sort) runs 15% faster under the C locale. The output between C and en_US.UTF8 is identical. We're considering moving our database from en_US.UTF8 to C, but we do deal with internationalized text. Well, C has reduced overhead for string comparisons, but obviously doesn't work well for international characters. The single-byte encodings have somewhat less overhead than UTF8. You can try using C locales for databases that don't require non-ASCII characters. To add: The middle ground I usually choose is to have a database encoding of UTF8 but with the C (aka POSIX) locale. This gives you the ability to store any unicode but indexing operations will use the faster C string comparison operations for a significant performance boost -- especially for partial string searches on an indexed column. This is an even more attractive option in 9.1 with the ability to specify specific collations at runtime. Good point! Thanks! -- // Dmitriy.
Re: [GENERAL] Views versus user-defined functions: formatting, comments, performance, etc.
On Wed, Aug 29, 2012 at 8:52 AM, Chris Travers chris.trav...@gmail.com wrote: ALTER TABLE fruit ADD apple_id int; ALTER TABLE fruit ADD FOREIGN KEY (apple_id, type) REFERENCES apple (fruit_id, type) DEFERRABLE INITIALLY DEFERRED; And then do the same for orange etc. you can then: ALTER TABLE fruit ADD CHECK ((type = 'apple' and apple_id IS NOT NULL) OR (type = 'orange' AND orange_id IS NOT NULL) etc Doing the above for me is a 'bridge too far'. Sure, It's the only way to make sure the base type is properly specialized but it just sucks. If there was some way to 'C union' the value into a single column (there isn't) i'd be all over it... merlin -- 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] Dropping a column on parent table doesn't propagate to children?
On Wed, Aug 29, 2012 at 3:06 PM, Vincent Veyron vv.li...@wanadoo.fr wrote: #1 Quote : Any indexes on the original table will not be created on the new table, unless the INCLUDING INDEXES clause is specified. This is referring to the behavior on creating a table LIKE another table. I am specifically asking about NOT using LIKE but just INHERITS. #2 I don't use schemas personnally, but if you do, I suggest you test and see what happens I will. Also, do you or does anyone know what the asterisk is for, that I asked about at the end of my original post? -- Moshe Jacobson Nead Werx, Inc. | Senior Systems Engineer 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com
Re: [GENERAL] psql unix env variables
Thanks -Original Message- From: Ryan Kelly [mailto:rpkell...@gmail.com] Sent: Wednesday, August 29, 2012 12:41 PM To: Little, Douglas Cc: PostgreSQL General (pgsql-general@postgresql.org) Subject: Re: [GENERAL] psql unix env variables On Wed, Aug 29, 2012 at 12:35:32PM -0500, Little, Douglas wrote: Is there a method for having unix env variables incorporated into a psql sql statement? Ie Export var='dev' Psql =c 'select count(*) from $var.customer;' export FOO=bar psql -c select count(*) from $FOO.customer; Note the double quotes. That allows your shell to interpolate the string into your query. Note that the interpolation is done by your shell, and not psql. Doug Little Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz Worldwide 500 W. Madison, Suite 1000 Chicago IL 60661| Office 312.260.2588 | Fax 312.894.5164 | Cell 847-997-5741 douglas.lit...@orbitz.commailto:douglas.lit...@orbitz.com [cid:image001.jpg@01CD85E2.C7732E50] orbitz.comhttp://www.orbitz.com/ | ebookers.comhttp://www.ebookers.com/ | hotelclub.comhttp://www.hotelclub.com/ | cheaptickets.comhttp://www.cheaptickets.com/ | ratestogo.comhttp://www.ratestogo.com/ | asiahotels.comhttp://www.asiahotels.com/ -Ryan Kelly -- 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] C locale versus en_US.UTF8. (Was: String comparision in PostgreSQL)
On Wed, Aug 29, 2012 at 01:45:20PM -0500, Merlin Moncure wrote: On Wed, Aug 29, 2012 at 12:43 PM, Bruce Momjian br...@momjian.us wrote: On Wed, Aug 29, 2012 at 10:31:21AM -0700, Aleksey Tsalolikhin wrote: On Wed, Aug 29, 2012 at 9:45 AM, Merlin Moncure mmonc...@gmail.com wrote: citext unfortunately doesn't allow for index optimization of LIKE queries, which IMNSHO defeats the whole purpose. to the best way remains to use lower() ... this will be index optimized and fast as long as you specified C locale for your database. What is the difference between C and en_US.UTF8, please? We see that the same query (that invokes a sort) runs 15% faster under the C locale. The output between C and en_US.UTF8 is identical. We're considering moving our database from en_US.UTF8 to C, but we do deal with internationalized text. Well, C has reduced overhead for string comparisons, but obviously doesn't work well for international characters. The single-byte encodings have somewhat less overhead than UTF8. You can try using C locales for databases that don't require non-ASCII characters. To add: The middle ground I usually choose is to have a database encoding of UTF8 but with the C (aka POSIX) locale. This gives you the ability to store any unicode but indexing operations will use the faster C string comparison operations for a significant performance boost -- especially for partial string searches on an indexed column. This is an even more attractive option in 9.1 with the ability to specify specific collations at runtime. Do you get proper sort ordering in this case, or only when you specific the proper collation at runtime? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] C locale versus en_US.UTF8. (Was: String comparision in PostgreSQL)
On Wed, Aug 29, 2012 at 12:52:50PM -0600, Scott Marlowe wrote: On Wed, Aug 29, 2012 at 11:43 AM, Bruce Momjian br...@momjian.us wrote: On Wed, Aug 29, 2012 at 10:31:21AM -0700, Aleksey Tsalolikhin wrote: On Wed, Aug 29, 2012 at 9:45 AM, Merlin Moncure mmonc...@gmail.com wrote: citext unfortunately doesn't allow for index optimization of LIKE queries, which IMNSHO defeats the whole purpose. to the best way remains to use lower() ... this will be index optimized and fast as long as you specified C locale for your database. What is the difference between C and en_US.UTF8, please? We see that the same query (that invokes a sort) runs 15% faster under the C locale. The output between C and en_US.UTF8 is identical. We're considering moving our database from en_US.UTF8 to C, but we do deal with internationalized text. Well, C has reduced overhead for string comparisons, but obviously doesn't work well for international characters. The single-byte encodings have somewhat less overhead than UTF8. You can try using C locales for databases that don't require non-ASCII characters. I think you're confusing encodings with locales. C is a locale. You I think technically C is a non-locale. can have a database with a locale of C and UTF-8 encoding. create database clocale_utf8 encoding='UTF8' LC_COLLATE= 'C' template=template0; \l Name | Owner | Encoding | Collate |Ctype| Access privileges --+--+---+-+-+--- clocale_utf8 | smarlowe | UTF8 | C | en_US.UTF-8 | SQL_ASCII is the encoding equivalent of C locale, but it also allows multi-byte characters. Yes, but what sort ordering do you get in that case? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] C locale versus en_US.UTF8. (Was: String comparision in PostgreSQL)
On Wed, Aug 29, 2012 at 2:17 PM, Bruce Momjian br...@momjian.us wrote: On Wed, Aug 29, 2012 at 12:52:50PM -0600, Scott Marlowe wrote: On Wed, Aug 29, 2012 at 11:43 AM, Bruce Momjian br...@momjian.us wrote: On Wed, Aug 29, 2012 at 10:31:21AM -0700, Aleksey Tsalolikhin wrote: On Wed, Aug 29, 2012 at 9:45 AM, Merlin Moncure mmonc...@gmail.com wrote: citext unfortunately doesn't allow for index optimization of LIKE queries, which IMNSHO defeats the whole purpose. to the best way remains to use lower() ... this will be index optimized and fast as long as you specified C locale for your database. What is the difference between C and en_US.UTF8, please? We see that the same query (that invokes a sort) runs 15% faster under the C locale. The output between C and en_US.UTF8 is identical. We're considering moving our database from en_US.UTF8 to C, but we do deal with internationalized text. Well, C has reduced overhead for string comparisons, but obviously doesn't work well for international characters. The single-byte encodings have somewhat less overhead than UTF8. You can try using C locales for databases that don't require non-ASCII characters. I think you're confusing encodings with locales. C is a locale. You I think technically C is a non-locale. True. But it's NOT an encoding. can have a database with a locale of C and UTF-8 encoding. create database clocale_utf8 encoding='UTF8' LC_COLLATE= 'C' template=template0; \l Name | Owner | Encoding | Collate |Ctype| Access privileges --+--+---+-+-+--- clocale_utf8 | smarlowe | UTF8 | C | en_US.UTF-8 | SQL_ASCII is the encoding equivalent of C locale, but it also allows multi-byte characters. Yes, but what sort ordering do you get in that case? Byte ordering. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Refreshing functional index
Hello, I have a problem with functional index feature in Postgres 8.3 There are two tables, lets call them: PARENTS and CHILDREN(with timestamp column) I created functional index on parents with function, which selects max value of timestamp from child elements(for given parent_id). The problem is that plpgsql function, which returns the value is IMMUTABLE and it works like a cache. When I insert new record to children table, select over parents with function gives wrong(outdated) results. So far I figured out only one way to flush this cache. It's by calling REINDEX on my index. I guess I should call it after every insert to children table. It's not good for me since it locks the table. I'm thinking about partitioning the index by my app, so reindexing will be less painful, but perhaps there is some other easier way to solve tihs problem? Thanks -- Regards, Grzegorz
Re: [GENERAL] Refreshing functional index
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Grzegorz Tanczyk Sent: Wednesday, August 29, 2012 5:02 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Refreshing functional index Hello, I have a problem with functional index feature in Postgres 8.3 There are two tables, lets call them: PARENTS and CHILDREN(with timestamp column) I created functional index on parents with function, which selects max value of timestamp from child elements(for given parent_id). The problem is that plpgsql function, which returns the value is IMMUTABLE and it works like a cache. When I insert new record to children table, select over parents with function gives wrong(outdated) results. So far I figured out only one way to flush this cache. It's by calling REINDEX on my index. I guess I should call it after every insert to children table. It's not good for me since it locks the table. I'm thinking about partitioning the index by my app, so reindexing will be less painful, but perhaps there is some other easier way to solve tihs problem? Thanks = This is not really a problem rather the behavior is working as designed and you are trying or expecting it to behave differently. First I would see whether I actually need an INDEX for whatever problem you are trying to solve. A view with an embedded sub-query to dynamically determine the max(timestamp) is likely to perform decently if there are not too many rows for each child. Any other suggestions require guessing about your data properties but if said timestamp is auto-assigned and thus always increasing as new children are added (and children are not deleted or change their timestamp) then adding an INSERT trigger on the child table that updates either the parent or some other relation-maintaining table would likely suffice. Also, your function is STABLE, not IMMUTABLE. Saying it is IMMUTABLE in order to create the index doesn't magically overcome the very reason that STABLE functions cannot be indexed. I would also suggest that using a timestamp is probably not the best decision. It is possible that two transactions could be started at the same time and thus the resultant timestamps would match as well. Also are you guaranteed that the times with always come from the same source? Again, they WHY behind your decision is unknown but storing the PK of the most recent child would make more sense conceptually rather than storing a time and having to back into the child. If you go that route upon INSERT you simply UPDATE parent SET child_id_mostrecent = child_pk. In the face of concurrency the last one to commit remains. Upon deletion, if allowed, you simply set it to NULL so that there is no most recent. Another option in that case would be to store an array and pre-pend each new child PK but also truncate the array to a maximum of, say 10 children. Upon delete you would then just remove any references to the deleted child from the array. For query purposes the first child is the one that matters (if present since an empty array is still possible). Updates could work the same way if you want to refresh the recentness of the children in that situation. Lots of options (mostly trigger on the child oriented) but a functional index is not one of them. David J. -- 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] Refreshing functional index
On Wed, Aug 29, 2012 at 4:01 PM, Grzegorz Tańczyk golia...@polzone.pl wrote: Hello, I have a problem with functional index feature in Postgres 8.3 There are two tables, lets call them: PARENTS and CHILDREN(with timestamp column) I created functional index on parents with function, which selects max value of timestamp from child elements(for given parent_id). The problem is that plpgsql function, which returns the value is IMMUTABLE and it works like a cache. When I insert new record to children table, select over parents with function gives wrong(outdated) results. So far I figured out only one way to flush this cache. It's by calling REINDEX on my index. I guess I should call it after every insert to children table. It's not good for me since it locks the table. I'm thinking about partitioning the index by my app, so reindexing will be less painful, but perhaps there is some other easier way to solve tihs problem? Well, the only reason what you're trying to do works at all is because the database isn't stricter about double checking to see if your stuff is IMMUTABLE: it isn't, so of course it doesn't work. How about a trigger on the child table that updates an indexed column on parent? merlin -- 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] String comparision in PostgreSQL
On 08/28/2012 10:46 PM, Nicola Cisternino wrote: 1) Why PostgreSQL don't use COLLATE to manage case sensitive / insensitive comparision (I think it's the best and ANSI standard way ) ? Support for per-column collations in PG was only added relatively recently - in 9.1, by the looks: http://www.postgresql.org/docs/9.1/static/collation.html Prior to that, there was no meaningful way to use case insensitive collations, as these would affect the whole database, including system tables, which could break all sorts of things in new and exciting ways. With the advent of per-column and per-operation collation control, case-insensitive collations become very appealing. One of the challenges with adding case insensitive collations is that, AFAIK, collations are implemented using the operating system charset and locale support, which may not offer case insensitive collation directly. Another challenge is the rather ... variable ... meaning of case insensitive. Results are likely to vary between host platforms and versions. Still, now that per-col / per-op collation is supported, it'd be nice to have. I don't know if it's just a matter of needing someone with the desire and time (or funding) and expertise to design and build it, or if there'd be issues with getting it accepted. -- Craig Ringer -- 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] SQLSTATE XX000 Internal Error 7
I know this post is a few months old now, but I have a strikingly similar setup and am getting a similar, somewhat more reliable error: ERROR: could not open file base/29292/12186914_fsm: Invalid argument It happens while doing an ST_Difference() function on two large geometries. I'm running OSX 10.8 (Mountain Lion) Postgresql 9.4.1 PostGIS 2.0.1 It's failing repeatedly for me right now, so at the moment it's a hard fail. That said, just last week, with the same database, same command but different table, I got this slightly different error: ERROR: could not open file base/29292/49722_fsm: Invalid argument THAT error, however, cleared up mysteriously after I shut down and restarted. (I haven't been able to get tonight's error to clear up in the same way.) What other information can I provide to help track squash this bug? Thanks, John -- View this message in context: http://postgresql.1045698.n5.nabble.com/SQLSTATE-XX000-Internal-Error-7-tp5682117p5721837.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] SQLSTATE XX000 Internal Error 7
On 08/30/2012 10:24 AM, johnkeefe wrote: I know this post is a few months old now, but I have a strikingly similar setup and am getting a similar, somewhat more reliable error: ERROR: could not open file base/29292/12186914_fsm: Invalid argument It happens while doing an ST_Difference() function on two large geometries. I'm running OSX 10.8 (Mountain Lion) Postgresql 9.4.1 Did you mean 8.4.1? -- Craig Ringer -- 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] SQLSTATE XX000 Internal Error 7
Ack. I meant 9.1.4: ~ ∴ psql psql (9.1.4) Thanks. -- View this message in context: http://postgresql.1045698.n5.nabble.com/SQLSTATE-XX000-Internal-Error-7-tp5682117p5721842.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
