Re: [GENERAL] Problem with async notifications of table updates
On Mar 19, 2008, at 2:35 AM, Tyler, Mark wrote: Alban Hertroys wrote: On Mar 18, 2008, at 3:58 AM, Tyler, Mark wrote: I suggest rethinking your dislike of NOTIFY. I have thought very hard about using NOTIFY for this but it has two large problems (from my point of view). The first is that it forces me to put far more smarts and state into the subscriber applications. This is because I cannot pass any information with the NOTIFY apart from the fact that "something happened". Due to this restriction my subscriber apps would have to go and look up some secondary table to get sufficient information to construct the real query. That is just plain ugly in my view. You will have the same problem if you want to send a message about a record change in combination with transactions. You can either send a message about an /uncommitted/ transaction and include what record changed, /or/ you send a message about a /committed/ transaction which possibly changed multiple of those records - in which case there's no possibility to send a single id along with your message. I think you are suggesting that instead of having the trigger function FOR EACH ROW it should be FOR EACH STATEMENT. In the case of an AFTER trigger using a FOR EACH STATEMENT clause; does it get fired / after/ the commit? (the documentation for CREATE TRIGGER does not really specify) I think FOR EACH ROW or FOR EACH STATEMENT will make little or no difference here, unless you can guarantee that you'll never change more than one row in a transaction. Rows changed in a transaction are usually in different tables, that's what transactions are for after all - "Transaction" is a good name for what it does. As an example in an employee database, if you want to send a NOTIFY when an employee's salary gets INSERTed, the transaction will usually also contain the INSERT of the employee record. You want those two statements together in a transaction, or you risk creating an employee without a salary (because the application crashed, the connection was lost, the office got flooded, etc) - and be sure that employee will be grumpy about that! For the difference between ON EACH ROW and ON EACH STATEMENT in triggers, that's best illustrated by queries such as INSERT INTO table1 (column1, column2, fkey1) SELECT 'value1', 'value2', id FROM table2 WHERE column1 = 'value3'; This single statement could result in multiple rows being inserted, and so you can either choose to fire a trigger for each inserted row or for the entire statement at once. That said, if you'll need to batch your statements for a client to be notified at the end of the transaction, sending a notify with an ON EACH STATEMENT trigger will yield NOTIFY's at the same moment in time (at COMMIT). That may work for me if I can guarantee that the publisher only ever changes single rows for notifiable tables in a transaction. That depends on the importance of your data's integrity in relation to transactions, as per the employee example above. I can't say anything about that. You could try sending a set after commit, equivalent to how INSERT RETURNING works, but you'll have to marshall those id's into your message yourself. And that's pretty similar to putting those id's in a table and fetch them from your application - it's just moving the work around. I prefer to put as much of the knowledge about the technicalities of the process into the publishing side of the system keeping the interface the subscriber apps have to deal with relatively simple. Anything I can do, within reason, to help this is good. Regards, Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,47e0a7459781583513226! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] tsearch2 in postgresql 8.3.1 - invalid byte sequence for encoding "UTF8": 0xc3
hi, i have an issue with tseach2, i just installed postgresql 8.3.1 on windows using UTF8 server encoding / client encoding and LOCALE Canada / French. CREATE DATABASE mydbWITH OWNER = me ENCODING = 'UTF8'; CREATE TABLE product ( product_id SERIAL NOT NULL, name VARCHAR(50)NOT NULL, description VARCHAR(1000) NOT NULL, CONSTRAINT pk_product PRIMARY KEY (product_id) ); ALTER TABLE product ADD COLUMN search_vector tsvector; CREATE INDEX idx_search_vector ON product USING gist(search_vector); UPDATE product SET search_vector = setweight(to_tsvector(name), 'A') || to_tsvector(description); ERROR: invalid byte sequence for encoding "UTF8": 0xc3 SQL state: 22021 Hint: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding". any idea why it's happening? pat -- 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] Problem with async notifications of table updates
Karsten Hilbert wrote: > On Tue, Mar 18, 2008 at 01:28:36PM +1030, Tyler, Mark wrote: >> This >> is because I cannot pass any information with the NOTIFY apart from >> the fact that "something happened". > > Oh, you can, you can calculate the name of the NOTIFY dynamically > in the trigger sending the notify, for example embedding a primary key value. I don't understand how this can work. Surely my subscriber applications have to listen to the same notification name that the publisher is going to notify on (unless I am missing something about how NOTIFY/LISTEN works). Yes I know you can construct that name dynamically prior to issuing the NOTIFY but if you are changing the notification name then how do the subscribers know what names to listen on? Do you have a quick example of your suggestion? Mark IMPORTANT: This email remains the property of the Australian Defence Organisation and is subject to the jurisdiction of section 70 of the CRIMES ACT 1914. If you have received this email in error, you are requested to contact the sender and delete the email. -- 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] Problem with async notifications of table updates
Alban Hertroys wrote: >On Mar 18, 2008, at 3:58 AM, Tyler, Mark wrote: >> I suggest rethinking your dislike of NOTIFY. >> >> I have thought very hard about using NOTIFY for this but it has two >> large problems (from my point of view). The first is that it forces me >> to put far more smarts and state into the subscriber applications. >> This >> is because I cannot pass any information with the NOTIFY apart from >> the fact that "something happened". Due to this restriction my >> subscriber apps would have to go and look up some secondary table to >> get sufficient information to construct the real query. That is just >> plain ugly in my view. > > You will have the same problem if you want to send a message about a > record change in combination with transactions. You can either send > a message about an /uncommitted/ transaction and include what record > changed, /or/ you send a message about a /committed/ transaction which > possibly changed multiple of those records - in which case there's no > possibility to send a single id along with your message. I think you are suggesting that instead of having the trigger function FOR EACH ROW it should be FOR EACH STATEMENT. In the case of an AFTER trigger using a FOR EACH STATEMENT clause; does it get fired /after/ the commit? (the documentation for CREATE TRIGGER does not really specify) That may work for me if I can guarantee that the publisher only ever changes single rows for notifiable tables in a transaction. > You could try sending a set after commit, equivalent to how INSERT RETURNING > works, but you'll have to marshall those id's into your message yourself. And > that's pretty similar to putting those id's in a table and fetch them from your > application - it's just moving the work around. I prefer to put as much of the knowledge about the technicalities of the process into the publishing side of the system keeping the interface the subscriber apps have to deal with relatively simple. Anything I can do, within reason, to help this is good. Mark IMPORTANT: This email remains the property of the Australian Defence Organisation and is subject to the jurisdiction of section 70 of the CRIMES ACT 1914. If you have received this email in error, you are requested to contact the sender and delete the email. -- 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] Problem with async notifications of table updates
Vivek Khera wrote: > Wait a while and you will learn to detest Spread, too. I know this is probably off-topic for this group but why do you say this? I guess I don't want to go too far down a particular route if there are big traps waiting so I am interested in the basis for your comment. Mark IMPORTANT: This email remains the property of the Australian Defence Organisation and is subject to the jurisdiction of section 70 of the CRIMES ACT 1914. If you have received this email in error, you are requested to contact the sender and delete the email. -- 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] Get index information from information_schema?
On Mar 18, 2008, at 5:42 PM, Dann Corbit wrote:
correct results for this query:
create table t1 (c1 int not null, c2 int not null, c3 char(5), c4 int,
c5 int, constraint pk_t1 PRIMARY KEY (c5,c4));
create table t2 (tc1 int not null, c1 int not null, c2 int not null,
c5
char(5), constraint fk_t2 FOREIGN KEY (c1,c2) references t1 (c5,c4));
select CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION from
information_schema.key_column_usage where TABLE_NAME in ('t1', 't2');
So the only thing that will be missing are the indexes that are none
of
the above.
It's a shame that there really is no information schema for indexes in
that category.
Careful with that, foreign keys are not at all indexes although it is
common practice to put indexes on columns with them.
Erik Jones
DBA | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.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] Get index information from information_schema?
> -Original Message-
> From: Erik Jones [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, March 18, 2008 11:51 AM
> To: Dann Corbit
> Cc: Albe Laurenz; AlannY *EXTERN*; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Get index information from information_schema?
>
>
> On Mar 18, 2008, at 1:28 PM, Dann Corbit wrote:
>
> >> -Original Message-
> >> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> >> [EMAIL PROTECTED] On Behalf Of Albe Laurenz
> >> Sent: Tuesday, March 18, 2008 1:24 AM
> >> To: AlannY *EXTERN*; pgsql-general@postgresql.org
> >> Subject: Re: [GENERAL] Get index information from
information_schema?
> >>
> >> AlannY wrote:
> >>> I need a method of extracting information about indexes of any
table
> >>> from information_schema.
> >>>
> >>> Have you any suggestions?
> >>
> >> I am afraid that indexes are not covered by information_schema.
> >>
> >> You'd have to dig into pg_catalog.pg_index for this.
> >
> > Doesn't the PostgreSQL schema have the
> > INFORMATION_SCHEMA.KEY_COLUMN_USAGE view?
> >
> > Or (conversely) are indexes not stored as constraints?
>
> Only if they are PRIMARY KEY or UNIQUE indexes.
It looks like Foreign keys are included as well, because I get the
correct results for this query:
create table t1 (c1 int not null, c2 int not null, c3 char(5), c4 int,
c5 int, constraint pk_t1 PRIMARY KEY (c5,c4));
create table t2 (tc1 int not null, c1 int not null, c2 int not null, c5
char(5), constraint fk_t2 FOREIGN KEY (c1,c2) references t1 (c5,c4));
select CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION from
information_schema.key_column_usage where TABLE_NAME in ('t1', 't2');
So the only thing that will be missing are the indexes that are none of
the above.
It's a shame that there really is no information schema for indexes in
that category.
--
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] Is autovacuum on?
On Wed, Mar 19, 2008 at 1:29 AM, Erik Jones <[EMAIL PROTECTED]> wrote: > SHOW autovacuum; That's even better, thanks Erik. Cheers, -Blair -- In science one tries to tell people, in such a way as to be understood by everyone, something that no one ever knew before. But in poetry, it's the exact opposite. - Paul Dirac -- 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] Conditional JOINs ?
On Mar 18, 2008, at 3:50 PM, Leon Mergen wrote: Ah, silly that I failed to understand that. Nah, we all do that stuff. Thanks a lot for your response (Alban too) -- I can see table partitioning solving my problem. You're welcome! Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.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] Conditional JOINs ?
Hello Erik,
On 3/18/08, Erik Jones <[EMAIL PROTECTED]> wrote:
> Table partitioning is normally implemented via table inheritance and
> you are free to add more, and different, columns to the "child" tables.
>
> Observe:
>
> CREATE SEQUENCE part_seq;
> CREATE TABLE parent (
> id integer PRIMARY KEY DEFAULT nextval('part_seq'),
> foo text
> );
>
> CREATE TABLE child1 (
> bar text,
> CHECK(foo='some_type1'),
> PRIMARY KEY (id)
> ) INHERITS (parent);
>
> CREATE TABLE child2 (
> baz text,
> CHECK(foo='some_type2'),
> PRIMARY KEY (id)
> ) INHERITS (parent);
>
> Now, both child1 and child2 have id and foo fields, child1 will only
> allow entries with foo='some_type1', child2 will only allow entries
> with foo='some_type2', and both children have extra fields that
> weren't present in the parent.
Ah, silly that I failed to understand that.
Thanks a lot for your response (Alban too) -- I can see table
partitioning solving my problem.
--
Leon Mergen
http://www.solatis.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] Conditional JOINs ?
On Mar 18, 2008, at 3:06 PM, Leon Mergen wrote:
Hello Alban,
On 3/18/08, Alban Hertroys <[EMAIL PROTECTED]>
wrote:
Now, in my theory, you would say that if postgresql encounters
ref1 =
NULL, it will not attempt to JOIN the log.requests1 table. However,
I've been told that because the PostgreSQL planner doesn't know that
ref1 (or any other refX for that matter) is NULL, it will attempt to
JOIN all tables for all rows.
Is this true, and if so.. is there a workaround for this (perhaps
that
my database design is flawed) ?
This looks almost like table partitioning. If you inherit your
requestxxx tables from a common requests table and add a check
constraint to each inheriting table (a "partition"), the planner is
smart enough to figure out that no rows in that partition can
possibly match (constraint exclusion) and skips it.
Instead of joining, it uses something equivalent to a UNION ALL btw,
which I think is what you're looking for anyway.
Well, the thing (as far as I'm aware) is that table partinioning and
UNION ALL expect the table layouts to look the same, don't they ? The
problem I'm having is that each row in a table has some 'additional'
information, which is in another table, and can be retrieved based on
a specific column in the table (request_type).
Now, I fail to see how UNION ALL or table partitioning can solve this
problem, which can be my problem -- am I missing some technique how
table partitioning can be used to extend a base table with several
extra tables that provide extra information ?
Table partitioning is normally implemented via table inheritance and
you are free to add more, and different, columns to the "child" tables.
Observe:
CREATE SEQUENCE part_seq;
CREATE TABLE parent (
id integer PRIMARY KEY DEFAULT nextval('part_seq'),
foo text
);
CREATE TABLE child1 (
bar text,
CHECK(foo='some_type1'),
PRIMARY KEY (id)
) INHERITS (parent);
CREATE TABLE child2 (
baz text,
CHECK(foo='some_type2'),
PRIMARY KEY (id)
) INHERITS (parent);
Now, both child1 and child2 have id and foo fields, child1 will only
allow entries with foo='some_type1', child2 will only allow entries
with foo='some_type2', and both children have extra fields that
weren't present in the parent.
Erik Jones
DBA | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Database recovery
Hello! My filesystem (UFS-FreeBSD) was crashed and I have lost files from PGSQL DATA DIR FOLDER after fsck-utility. I have lost files from directory "pg_xlog" I reset my pg_xlog. And now have such error by starting pgsql-server: LOG: database system was interrupted at 2008-03-18 22:29:48 EET LOG: checkpoint record is at 3E/6C20 LOG: redo record is at 3E/6C20; undo record is at 3E/6C20; shutdown TRUE LOG: next transaction ID: 0/1624775486; next OID: 9528514 LOG: next MultiXactId: 6643; next MultiXactOffset: 13690 LOG: database system was not properly shut down; automatic recovery in progress LOG: record with zero length at 3E/6C68 LOG: redo is not required FATAL: could not access status of transaction 6643 DETAIL: Could not read from file "pg_multixact/offsets/" at offset 24576: Success. LOG: startup process (PID 6267) exited with exit code 1 LOG: aborting startup due to startup process failure LOG: logger shutting down Is it possible to lose this problem? -- 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] Conditional JOINs ?
Hello Alban, On 3/18/08, Alban Hertroys <[EMAIL PROTECTED]> wrote: > > Now, in my theory, you would say that if postgresql encounters ref1 = > > NULL, it will not attempt to JOIN the log.requests1 table. However, > > I've been told that because the PostgreSQL planner doesn't know that > > ref1 (or any other refX for that matter) is NULL, it will attempt to > > JOIN all tables for all rows. > > > > Is this true, and if so.. is there a workaround for this (perhaps that > > my database design is flawed) ? > > > This looks almost like table partitioning. If you inherit your > requestxxx tables from a common requests table and add a check > constraint to each inheriting table (a "partition"), the planner is > smart enough to figure out that no rows in that partition can > possibly match (constraint exclusion) and skips it. > > Instead of joining, it uses something equivalent to a UNION ALL btw, > which I think is what you're looking for anyway. Well, the thing (as far as I'm aware) is that table partinioning and UNION ALL expect the table layouts to look the same, don't they ? The problem I'm having is that each row in a table has some 'additional' information, which is in another table, and can be retrieved based on a specific column in the table (request_type). Now, I fail to see how UNION ALL or table partitioning can solve this problem, which can be my problem -- am I missing some technique how table partitioning can be used to extend a base table with several extra tables that provide extra information ? -- Leon Mergen http://www.solatis.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] Conditional JOINs ?
On Mar 18, 2008, at 8:06 PM, Leon Mergen wrote: Hello, Now, based on a previous post on the PostgreSQL mailing list [http://archives.postgresql.org/pgsql-general/2007-11/msg00723.php] I came up with this solution: ### -- table where all common request data is stored CREATE TABLE log.requests ( id BIGSERIAL, type SMALLINT NOT NULL, ref1 BIGINT CHECK (type = 1) = (ref1 IS NOT NULL)), ref2 BIGINT CHECK (type = 2) = (ref2 IS NOT NULL)), timestamp INTEGER NOT NULL, -- all kinds of common data ) -- only rows where log.requests.type = 1 CREATE TABLE log.requests1 ( request_id BIGINT NOT NULL ) -- only rows where log.requests.type = 2 CREATE TABLE log.requestsz2 ( request_id BIGINT NOT NULL ) etc (about 10 different types) the current VIEW: CREATE VIEW requests_view AS SELECT * FROM log.requests LEFT JOIN log.requests1 ON (log.requests.ref1 = log.requests1.request_id) LEFT JOIN log.requests2 ON (log.requests.ref2 = log.requests2.request_id) -- etc ### Now, in my theory, you would say that if postgresql encounters ref1 = NULL, it will not attempt to JOIN the log.requests1 table. However, I've been told that because the PostgreSQL planner doesn't know that ref1 (or any other refX for that matter) is NULL, it will attempt to JOIN all tables for all rows. Is this true, and if so.. is there a workaround for this (perhaps that my database design is flawed) ? This looks almost like table partitioning. If you inherit your requestxxx tables from a common requests table and add a check constraint to each inheriting table (a "partition"), the planner is smart enough to figure out that no rows in that partition can possibly match (constraint exclusion) and skips it. Instead of joining, it uses something equivalent to a UNION ALL btw, which I think is what you're looking for anyway. There have been plenty of posts about how to set up table partitioning, there's even an article about it in the standard Postgres documentation: http://www.postgresql.org/docs/8.1/static/ddl-partitioning.html Regards, Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,47e019899786732118417! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Conditional JOINs ?
Hello, I'm currently camping with a bit of a problem -- i have a common requests table, and based on an entry's type, I need to join a specific other table. Consider this database layout: ### -- table where all common request data is stored CREATE TABLE log.requests ( id BIGSERIAL, type SMALLINT NOT NULL, timestamp INTEGER NOT NULL, -- all kinds of common data ) -- only rows where log.requests.type = 1 CREATE TABLE log.requests1 ( request_id BIGINT NOT NULL ) -- only rows where log.requests.type = 2 CREATE TABLE log.requests2 ( request_id BIGINT NOT NULL ) etc (about 10 different types) the current VIEW: CREATE VIEW requests_view AS SELECT * FROM log.requests LEFT JOIN log.requests1 ON (log.requests.id = log.requests1.request_id) LEFT JOIN log.requests2 ON (log.requests.id = log.requests2.request_id) -- etc ### As you can see, if the requests table gets a bit large (50mil+ rows) and when there are many different types, there will be a lot of wasted JOINs. Now, based on a previous post on the PostgreSQL mailing list [http://archives.postgresql.org/pgsql-general/2007-11/msg00723.php] I came up with this solution: ### -- table where all common request data is stored CREATE TABLE log.requests ( id BIGSERIAL, type SMALLINT NOT NULL, ref1 BIGINT CHECK (type = 1) = (ref1 IS NOT NULL)), ref2 BIGINT CHECK (type = 2) = (ref2 IS NOT NULL)), timestamp INTEGER NOT NULL, -- all kinds of common data ) -- only rows where log.requests.type = 1 CREATE TABLE log.requests1 ( request_id BIGINT NOT NULL ) -- only rows where log.requests.type = 2 CREATE TABLE log.requestsz2 ( request_id BIGINT NOT NULL ) etc (about 10 different types) the current VIEW: CREATE VIEW requests_view AS SELECT * FROM log.requests LEFT JOIN log.requests1 ON (log.requests.ref1 = log.requests1.request_id) LEFT JOIN log.requests2 ON (log.requests.ref2 = log.requests2.request_id) -- etc ### Now, in my theory, you would say that if postgresql encounters ref1 = NULL, it will not attempt to JOIN the log.requests1 table. However, I've been told that because the PostgreSQL planner doesn't know that ref1 (or any other refX for that matter) is NULL, it will attempt to JOIN all tables for all rows. Is this true, and if so.. is there a workaround for this (perhaps that my database design is flawed) ? Regards, Leon Mergen -- 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] Get index information from information_schema?
On Mar 18, 2008, at 1:28 PM, Dann Corbit wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-general- [EMAIL PROTECTED] On Behalf Of Albe Laurenz Sent: Tuesday, March 18, 2008 1:24 AM To: AlannY *EXTERN*; pgsql-general@postgresql.org Subject: Re: [GENERAL] Get index information from information_schema? AlannY wrote: I need a method of extracting information about indexes of any table from information_schema. Have you any suggestions? I am afraid that indexes are not covered by information_schema. You'd have to dig into pg_catalog.pg_index for this. Doesn't the PostgreSQL schema have the INFORMATION_SCHEMA.KEY_COLUMN_USAGE view? Or (conversely) are indexes not stored as constraints? Only if they are PRIMARY KEY or UNIQUE indexes. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.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] Feature request/suggestion - CREATE SCHEMA LIKE
On Mon, Mar 17, 2008 at 9:01 PM, wstrzalka <[EMAIL PROTECTED]> wrote:
> Hi
>
>Features like CREATE DATABASE WITH TEMPLATE or CREATE TABLE LIKE
> are very usefull but it would be great to have such a feature on the
> mid-level too. I mean something CREATE SCHEMA LIKE that would copy all
> the template schema relations, etc...
> What do you think about it ? Would it be hard to implement ? Is it
> worth the effort ?
I think it is a bit too complicated for the backend -- you
have to copy functions, views, types along the tables.
And most importantly -- their dependencies (for the order in
which to create them). Chances are that user defined
functions won't work in new schema. Tricky to say the least.
Perhaps a pg_dump -s with an option to "rename" the schema
would be a better option to consider (sed(1) is a good friend,
but IMHO explicit option would be much better).
If you insist in putting it in database -- a PL/pgSQL
function would be the best approach IMHO, something along:
CREATE OR REPLACE FUNCTION create_schema_like(old_name name, new_name
name) RETURNS void AS $$
DECLARE
rel_name name;
old_schema text;
new_schema text;
ddl text;
path text;
BEGIN
path := current_setting('search_path');
old_schema := quote_ident(old_name);
new_schema := quote_ident(new_name);
EXECUTE 'CREATE SCHEMA '||new_schema;
FOR rel_name IN SELECT tablename FROM pg_tables WHERE
schemaname=old_schema LOOP
ddl := 'CREATE TABLE
'||new_schema||'.'||quote_ident(rel_name)
||' (LIKE '||old_schema||'.'||rel_name
||' INCLUDING DEFAULTS INCLUDING
CONSTRAINTS INCLUDING INDEXES)';
EXECUTE ddl;
END LOOP;
-- If we set search_path to old schema, definitions will have
schemanames from other schemas prepended where necessary
EXECUTE 'SET LOCAL search_path TO '||old_schema;
FOR rel_name, ddl IN SELECT viewname,definition FROM
pg_views WHERE schemaname = old_name LOOP
EXECUTE 'SET LOCAL search_path TO '||new_schema;
ddl := 'CREATE VIEW
'||quote_ident(rel_name)||' AS '||ddl;
EXECUTE ddl;
END LOOP;
EXECUTE 'SET LOCAL search_path TO '||path;
RETURN;
END;
$$ LANGUAGE PLpgSQL STRICT;
Of course you need also to:
* copy functions, types, etc, etc.
* pray that dependencies are met or get acquainted with pg_depend :)
* take care of ownerships, ACLs and tablespaces
In my opinion this is way too complicated to put it inside the backend.
It is mostly already inside pg_dump, so either pg_dump|sed|psql or
TODO: pg_dump: optional parameter for renaming schemas (and
tablespaces, and owners).
Regards,
Dawid
--
Solving [site load issues] with [more database replication] is a lot
like solving your own personal problems with heroin - at first it
sorta works, but after a while things just get out of hand.
- Fred B. Schneider, PhD
--
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] Get index information from information_schema?
> -Original Message- > From: [EMAIL PROTECTED] [mailto:pgsql-general- > [EMAIL PROTECTED] On Behalf Of Albe Laurenz > Sent: Tuesday, March 18, 2008 1:24 AM > To: AlannY *EXTERN*; pgsql-general@postgresql.org > Subject: Re: [GENERAL] Get index information from information_schema? > > AlannY wrote: > > I need a method of extracting information about indexes of any table > > from information_schema. > > > > Have you any suggestions? > > I am afraid that indexes are not covered by information_schema. > > You'd have to dig into pg_catalog.pg_index for this. Doesn't the PostgreSQL schema have the INFORMATION_SCHEMA.KEY_COLUMN_USAGE view? Or (conversely) are indexes not stored as constraints? -- 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] [HACKERS] Collating records based on a custom group by (aggregate like) function
On Tue, Mar 18, 2008 at 9:00 AM, Dan Searle <[EMAIL PROTECTED]> wrote: > I've racked my brain about this but can't think of a simple solution, > even though this appears to be a simple problem, any suggestions much > appreciated. Your fact is split across more than one row. I recommend that you refactor the table to include start/end time pairs, then you'll have a complete fact per row, and a chance for more efficient temporal data retrieval. If that's not possible, consider creating a new helper table with start/end times and using a trigger on the main table to update/insert rows on the helper table, then querying from it. If none of that is possible, then you'll have to resort to writing kludges like self joins or something like this ... SELECT e, MIN(t), MAX(t) FROM t GROUP BY e, MOD(EXTRACT(MINUTE FROM t)::INT / 5, 60 / 5) ; ... which works for your example data, but it's pretty clear this will break if/when there are overlapping hours, etc. Last, but not least, I recommend you read this: Developing Time-Oriented Database Applications in SQL by Richard T. Snodgrass Publications link: http://www.cs.arizona.edu/~rts/publications.html PDF link: http://www.cs.arizona.edu/~rts/tdbbook.pdf In any case, good luck. -- 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] 8.3.0 upgrade
Hi, On Mon, 2008-03-17 at 21:26 -0500, Adam Rich wrote: > Next, one of my apps failed because of a dependency on libpq.so.4. > During previous upgrades, I remedied that by installing this package: > > compat-postgresql-libs-4-2PGDG.rhel4 > > But it seems under the 8.3.0 binary downloads, this package is no > longer available. The only compat package is > "compat-postgresql-libs-3" which of course includes only libpq.so.3 > so I had to browse older releases to find the missing version which I > thought seemed a little odd. Pick your OS/Arch from this list, and click to it: http://yum.pgsqlrpms.org/rpmchart.php Then click to "C" at the top, and download the compat package. Also, you can use that repository, so that it will take care of the dependencies: http://yum.pgsqlrpms.org/howtoyum.php Regards, -- Devrim GÜNDÜZ , RHCE PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/ signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Feature request/suggestion - CREATE SCHEMA LIKE
Joris Dobbelsteen wrote: > wstrzalka wrote: > > Hi > > > >Features like CREATE DATABASE WITH TEMPLATE or CREATE TABLE LIKE > > are very usefull but it would be great to have such a feature on the > > mid-level too. I mean something CREATE SCHEMA LIKE that would copy all > > the template schema relations, etc... > > What do you think about it ? Would it be hard to implement ? Is it > > worth the effort > I believe the CREATE DATABASE was because of the way postgresql creates > a database. I thought it just copied the template database (but are not > completely sure). I also believe CREATE TABLE LIKE was mostly for > temporary tables, where the query can not be sure what the table > actually looks like when it is invoked. > > Would it not be possible to work around the SCHEMA LIKE by just dumping > the database schema and restoring it to a new schema? This seems more > like a part that should be under strict user control and not automated > by common queries. No one has actually asked for CREATE SCHEMA LIKE before but we could add it to the TODO list if we can find a few people who want the feature. -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Problem with async notifications of table updates
On Mar 17, 2008, at 10:58 PM, Tyler, Mark wrote: I suggest rethinking your dislike of NOTIFY. I have thought very hard about using NOTIFY for this but it has two large problems (from my point of view). The first is that it forces me Wait a while and you will learn to detest Spread, too. -- 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] Is autovacuum on?
On Mar 17, 2008, at 11:25 PM, Blair Bethwaite wrote: On Tue, Mar 18, 2008 at 3:20 PM, Filip Rembiałkowski <[EMAIL PROTECTED]> wrote: yes. select setting from pg_settings where name = 'autovacuum'; Ah ha, thankyou! I assumed there must have been a view for the settings, I guess I missed it when I looked at the various pg_* views. Or, a simple: SHOW autovacuum; will to the trick. http://www.postgresql.org/docs/8.2/interactive/sql-show.html Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Create user trigger?
Hi Chaps, We're setting up 3 servers replicating using slony. I was wondering if it'd be possible for me to create a set of triggers that fire whenever a user is created/dropped/modified on one of the servers that goes and performs the same action the other two servers. Does that sound doable? I'm not sure what the implications of doing this would be, and I'mm not sure the best way to do it But this is what I was thinking of doing: Have a user table containing all the usernames, and every time a user is inserted/deleted from the table create/drop the user using a trigger. Then put the table in replication using slony. Obviously I'd have to drop and then recreate the users when I wanted to alter them, and I'd have to meke sure permissions were set properly to the table. Are there any better alternatives? Thanks ___ Rise to the challenge for Sport Relief with Yahoo! For Good http://uk.promotions.yahoo.com/forgood/ -- 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] Problem with async notifications of table updates
On Tue, Mar 18, 2008 at 01:28:36PM +1030, Tyler, Mark wrote: > This > is because I cannot pass any information with the NOTIFY apart from the > fact that "something happened". Oh, you can, you can calculate the name of the NOTIFY dynamically in the trigger sending the notify, for example embedding a primary key value. This may or may not be of help in your application though. The whole issue is on the TODO list anyhow, AFAIR. > Secondly, the lack of any delivery guarantee means my subscriber Only the *quantity* is not guaranteed. You will always get *one* of several notifies of the same type (provided you listen). Again, this may or may not be sufficient for your application but it's certainly not "lack of *any* guarantee". Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- 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] Feature request/suggestion - CREATE SCHEMA LIKE
wstrzalka wrote: Hi Features like CREATE DATABASE WITH TEMPLATE or CREATE TABLE LIKE are very usefull but it would be great to have such a feature on the mid-level too. I mean something CREATE SCHEMA LIKE that would copy all the template schema relations, etc... What do you think about it ? Would it be hard to implement ? Is it worth the effort I believe the CREATE DATABASE was because of the way postgresql creates a database. I thought it just copied the template database (but are not completely sure). I also believe CREATE TABLE LIKE was mostly for temporary tables, where the query can not be sure what the table actually looks like when it is invoked. Would it not be possible to work around the SCHEMA LIKE by just dumping the database schema and restoring it to a new schema? This seems more like a part that should be under strict user control and not automated by common queries. - Joris -- 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] Get index information from information_schema?
AlannY wrote: > I need a method of extracting information about indexes of any table > from information_schema. > > Have you any suggestions? I am afraid that indexes are not covered by information_schema. You'd have to dig into pg_catalog.pg_index for this. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is autovacuum on?
On Tue, Mar 18, 2008 at 3:20 PM, Filip Rembiałkowski <[EMAIL PROTECTED]> wrote: > yes. > select setting from pg_settings where name = 'autovacuum'; Ah ha, thankyou! I assumed there must have been a view for the settings, I guess I missed it when I looked at the various pg_* views. Cheers, -Blair -- In science one tries to tell people, in such a way as to be understood by everyone, something that no one ever knew before. But in poetry, it's the exact opposite. - Paul Dirac -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Feature request/suggestion - CREATE SCHEMA LIKE
Hi Features like CREATE DATABASE WITH TEMPLATE or CREATE TABLE LIKE are very usefull but it would be great to have such a feature on the mid-level too. I mean something CREATE SCHEMA LIKE that would copy all the template schema relations, etc... What do you think about it ? Would it be hard to implement ? Is it worth the effort ? -- 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] identify database process given client process
On Mon, Mar 17, 2008 at 8:28 PM, hogcia <[EMAIL PROTECTED]> wrote: > Hi, > I have to find a Postgres database process pid (or other > identification) for a given client process pid. Or client processes > for a database process. How are they connected? I was suggested maybe > netstat could give me the answer and I think those are two pf_unix > processes. But maybe there are some PostgreSQL functions that do this? > How should I approach this topic? > If possible, execute the following query from your client, and it will show that server process the client is connected to. select pg_backend_pid(); This is an easy, one way route to determine a client's shadow process, be it local or remote. If you want reverse lookup, that is, trying to find out which backend process is servicing which client, it'd be a bit difficult. You have to query pg_stat_activity and the client_addr and client_port columns wil give you some information about the remote clients. For local clients using Unix sockets, these columns are null. Best regards, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com 17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad * 18° 32' 57.25"N, 73° 56' 25.42"E - Pune 37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco http://gurjeet.frihost.net Mail sent from my BlackLaptop device
Re: [GENERAL] Problem with async notifications of table updates
On Mar 18, 2008, at 3:58 AM, Tyler, Mark wrote: I suggest rethinking your dislike of NOTIFY. I have thought very hard about using NOTIFY for this but it has two large problems (from my point of view). The first is that it forces me to put far more smarts and state into the subscriber applications. This is because I cannot pass any information with the NOTIFY apart from the fact that "something happened". Due to this restriction my subscriber apps would have to go and look up some secondary table to get sufficient information to construct the real query. That is just plain ugly in my view. You will have the same problem if you want to send a message about a record change in combination with transactions. You can either send a message about an /uncommitted/ transaction and include what record changed, /or/ you send a message about a /committed/ transaction which possibly changed multiple of those records - in which case there's no possibility to send a single id along with your message. You could try sending a set after commit, equivalent to how INSERT RETURNING works, but you'll have to marshall those id's into your message yourself. And that's pretty similar to putting those id's in a table and fetch them from your application - it's just moving the work around. Regards, Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,47df69e69781418010441! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
