Re: [SQL] Table Partitioning and Rules
On Thursday 17 Jul 2003 8:39 pm, Steve Crawford wrote: > OK, so basically you are trying to keep a hundered some odd attributes on > everyone in the US. It's possible that a 150 column table is properly > normalized (I have a similar situation) but it is rare. > > Suppose it is really properly normalized. You can still benefit from > indexes on just some of the columns by choosing those most commonly used in > queries. You may also want to research partial indexes (create index foo > ... where bar=baz) which can under certain circumstances be far smaller and > faster than full indexes. The other issue is, that even if your table is normalised you may want to split vertically. That's going to depend on usage patterns, and I don't know what you've got, but say you used three tables: contact_address contact_personal contact_bank_details Now a search by sales would be interested in ...address & ...personal whereas accounts would look at ...address & ...bank_details. This _might_ make sense, but probably only if you can group columns into related groups and users are more interested in some groups than others. If you are lucky the gains might be in cache usage, whereas the costs will be in joining groups for results. I'm not saying you should do this just to try and improve performance, but it might make sense if users look at it that way. > Review your structure carefully. Plan on $$$ for the hardware. Or remind your users that patience is a virtue ;-) -- Richard Huxton ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] (trigger function) -> ERROR: NEW used in non-rule query
I'm writing a trigger which wants to reference the pre-defined NEW record. I can do this idval := NEW.blah; This works fine!!! What I really need to do is idval := NEW.(quote_ident(TG_ARGV[3])); or this idval := NEW.(TG_ARGV[3]); unfortunately this returns "ERROR: NEW used in non-rule query" I've also tried using EXECUTE ''SELECT NEW.'' || quote_ident(TG_ARGV[3]); (to test if I can use a FOR-IN-EXECUTE to extract it) and it produces exactly the same error. Obviously NEW goes out of scope for some reason. I can understand the case with EXECUTE but the other statements should have worked :( This is very frustrating because it is the *ONLY* thing standing in the way of me solving a larger problem (which I will use to repsond to another thread in this forum). Does anyone know how to access an arbitarily specified field of the NEW record in a trigger function. I need this because arguments to a function in the trigger definition cannot be NEW.blah, they have to be static/literals (which is likely to be for the same reason that NEW goes out of scope any time I try to do something dynamic when evaluating a field on it). FYI: ref doco at http://www.postgresql.org/docs/7.3/interactive/plpgsql-trigger.html using pg 7.3.3 -- Terence Kearns ~ ph: +61 2 6201 5516 IT Database/Applications Developer Enterprise Information Systems Client Services Division University of Canberra www.canberra.edu.au ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] (trigger function) -> ERROR: NEW used in non-rule query
On Friday 18 Jul 2003 9:22 am, Terence Kearns wrote: > I'm writing a trigger which wants to reference the pre-defined NEW record. > > I can do this > idval := NEW.blah; > > This works fine!!! > > What I really need to do is > idval := NEW.(quote_ident(TG_ARGV[3])); > or this > idval := NEW.(TG_ARGV[3]); I'll give you the short answer - no you can't do this (in plpgsql). Which isn't to say it'll never be possible, but not at the moment. Can you afford to look at an alternative language? I'd suggest looking at TCL, although I must admit I've never used it myself. -- Richard Huxton ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] (trigger function) -> ERROR: NEW used in non-rule query
Richard Huxton wrote:
On Friday 18 Jul 2003 9:22 am, Terence Kearns wrote:
I'm writing a trigger which wants to reference the pre-defined NEW record.
I can do this
idval := NEW.blah;
This works fine!!!
What I really need to do is
idval := NEW.(quote_ident(TG_ARGV[3]));
or this
idval := NEW.(TG_ARGV[3]);
I'll give you the short answer - no you can't do this (in plpgsql). Which
isn't to say it'll never be possible, but not at the moment.
Can you afford to look at an alternative language? I'd suggest looking at TCL,
although I must admit I've never used it myself.
damn. There's realy no other way for me to access the value since the
trigger function will be called from different relations.
I tried idval := (''NEW.'' || TG_ARGV[3]); but recieved
ERROR: pf_atoi: error in "NEW.blah": can't parse "NEW.blah"
Oh well, can't win them all :/
Well I suppose I could try TCL. The problem is that there is little to
no documentation on postgres stored procedures in TCL and I've never
even seen the language before. None the less, I'll look into it. It's
almost worth it. If that fails, I may even try perl . if that
files, I will try to get --with-python to configure (which it's refusing
to do at the moment even though I have a working installation).
--
Terence Kearns ~ ph: +61 2 6201 5516
IT Database/Applications Developer
Enterprise Information Systems
Client Services Division
University of Canberra
www.canberra.edu.au
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] (trigger function) -> ERROR: NEW used in non-rule query
On Friday 18 July 2003 10:18, Terence Kearns wrote:
> damn. There's realy no other way for me to access the value since the
> trigger function will be called from different relations.
>
> I tried idval := (''NEW.'' || TG_ARGV[3]); but recieved
> ERROR: pf_atoi: error in "NEW.blah": can't parse "NEW.blah"
>
> Oh well, can't win them all :/
Well, not this one at the moment, anyway.
> Well I suppose I could try TCL. The problem is that there is little to
> no documentation on postgres stored procedures in TCL and I've never
> even seen the language before. None the less, I'll look into it. It's
> almost worth it. If that fails, I may even try perl . if that
> files, I will try to get --with-python to configure (which it's refusing
> to do at the moment even though I have a working installation).
Not sure if you can write triggers in perl, and I think python is going
untrusted only (which means you need to be an admin to create functions).
- Richard Huxton
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] How to determine the currently logged on username
Hi all; I will be writing a stored proceedure that will allow a currently logged in user to change his/her password. The function needs to be only able to change the password of the currently logged in user, so it will only take a varchar() argument and needs to look up the username of the currently logged in user. How do I do this? Any ideas? Best Wishes, Chris Travers ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How to determine the currently logged on username
On Friday 18 July 2003 14:41, Chris Travers wrote: > Hi all; > > I will be writing a stored proceedure that will allow a currently logged > in user to change his/her password. The function needs to be only able > to change the password of the currently logged in user, so it will only > take a varchar() argument and needs to look up the username of the > currently logged in user. How do I do this? Any ideas? select CURRENT_USER; -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] [HACKERS] plpgsql strangeness with select into
Reinoud, First, I'm moving your question to PGSQL-SQL, which is the appropriate list, not HACKERS. See my response at the bottom of your quote. > I'm debugging a trigger in plpgsql and for some reason or the "select > into " does not seem to work. Here is an unaltered snippet of my > trigger code: > >raise notice ''this id : %'',NEW.id; > >select into i_hierarchy_id > hierarchy_id > from link_def LD, > link L, > object_link OL > where OL.id = NEW.id > and L.id = OL.link_id > and LD.id = L.link_def_id; > > raise notice ''i_hierarchy_id: %'',i_hierarchy_id; > > > in the log this results in: > > NOTICE: this id : 5265 > NOTICE: i_hierarchy_id: > > but when I perform the query on the command line I do get a result: > > select hierarchy_id > from link_def LD, >link L, >object_link OL > where OL.id = 5264 >and L.id = OL.link_id >and LD.id = L.link_def_id; > > hierarchy_id > -- > 1 > (1 row) > > i_hierarchy_id is declared as integer and is not used before this code nor > as a column name anywhere. > > Does anyone have a clue what is going wrong? I use Postgresql 7.3.3 on > FreeBSD 4.5. Without seeing your full trigger code, I can't tell for sure. However, I would guess that your "SELECT INTO" statement is querying data that has not yet been created; it's an FK record waiting on a deferred trigger, or you're using a BEFORE trigger and querying the record which has not yet been committed. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [HACKERS] plpgsql strangeness with select into
On Fri, 18 Jul 2003, Josh Berkus wrote: > > I'm debugging a trigger in plpgsql and for some reason or the "select > > into " does not seem to work. Here is an unaltered snippet of my > > trigger code: > > > >raise notice ''this id : %'',NEW.id; > > > >select into i_hierarchy_id > > hierarchy_id > > from link_def LD, > > link L, > > object_link OL > > where OL.id = NEW.id > > and L.id = OL.link_id > > and LD.id = L.link_def_id; > > > > raise notice ''i_hierarchy_id: %'',i_hierarchy_id; > > > > > > in the log this results in: > > > > NOTICE: this id : 5265 > > NOTICE: i_hierarchy_id: > > > > but when I perform the query on the command line I do get a result: > > > > select hierarchy_id > > from link_def LD, > >link L, > >object_link OL > > where OL.id = 5264 > >and L.id = OL.link_id > >and LD.id = L.link_def_id; > > > > hierarchy_id > > -- > > 1 > > (1 row) > > > > i_hierarchy_id is declared as integer and is not used before this code nor > > as a column name anywhere. > > > > Does anyone have a clue what is going wrong? I use Postgresql 7.3.3 on > > FreeBSD 4.5. > > Without seeing your full trigger code, I can't tell for sure. However, I > would guess that your "SELECT INTO" statement is querying data that has not > yet been created; it's an FK record waiting on a deferred trigger, or you're > using a BEFORE trigger and querying the record which has not yet been > committed. Or perhaps you have a variable named hierarchy_id in the function which is getting used rather than the column of the table in question. Seeing the entire function might help. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] (trigger function) -> ERROR: NEW used in non-rule query
Terence, > Oh well, can't win them all :/ Nope. I'll suggest that for the TODO list ... we already have several requests for added features for PL/pgSQL. The problem is that we currently don't have a lead developer for PL/pgSQL, so the language has rather stagnated. > Well I suppose I could try TCL. The problem is that there is little to > no documentation on postgres stored procedures in TCL and I've never > even seen the language before. None the less, I'll look into it. It's > almost worth it. If that fails, I may even try perl . And what's wrong with Perl? Other than the inability to write triggers with it? (We want to enable triggers in PL/perl, but that functionality isn't coming until at least 7.5). -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] (trigger function) -> ERROR: NEW used in non-rule query
Josh Berkus wrote: Well I suppose I could try TCL. The problem is that there is little to no documentation on postgres stored procedures in TCL and I've never even seen the language before. None the less, I'll look into it. It's almost worth it. If that fails, I may even try perl . And what's wrong with Perl? Other than the inability to write triggers with it? That's *exactly* "what's wrong" with it :-) If I understand what he is talking about correctly, his whole problem is that he is writing a trigger :-) Dima ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] how to copy table to another database?
Hi,Anyone know how the procedure or commands to copy table to another database.or querying from another database if possible?thank youyudie
Re: [SQL] how to copy table to another database?
Yudie wrote: Hi, Anyone know how the procedure or commands to copy table to another database. or querying from another database if possible? thank you yudie Something like this, perhaps? psql -d first_database -c '\copy mytable to stdout' | psql -d second_database -c '\copy mytable from stdin' I hope, it helps... Dima ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] how to copy table to another database?
On Fri, 18 Jul 2003, Yudie wrote: > Hi, > Anyone know how the procedure or commands to copy table to another database. > or querying from another database if possible? If you want to copy a table from one db to another, you can use this: pg_dump dbname -t tablename |psql dbname -e If you want cross database queries, look in the /contrib/dblink dir in the tarball of postgresql ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] casting to arrays
I have a rather odd table structure that I would like to simplify to be a view (for some definition of simplify). The current idea I have is to shovel values from multiple rows in one table into an array in the view. The tables look something like this: create table person ( id serial, name varchar ); create table stuff ( person_id references person (id) on delete restrict, stuff_name varchar ); The view would go something like: create view person_with_stuff as select p.id as id, p.name as name, ( select s.stuff_name from stuff where s.person_id = p.id )::varchar[] from person p; Is anything like this possible? I know this may not be good form, but unfortunately (or perhaps fortunately, since it means I have a job) there are business reasons for this, supporting old apps and such. Thanks in advance! -- Mike Rylander ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] casting to arrays
Mike Rylander wrote:
I have a rather odd table structure that I would like to simplify to be a view
(for some definition of simplify). The current idea I have is to shovel
values from multiple rows in one table into an array in the view. The tables
look something like this:
Is anything like this possible? I know this may not be good form, but
unfortunately (or perhaps fortunately, since it means I have a job) there are
business reasons for this, supporting old apps and such.
Not possible in current releases, but it will be in 7.4 (about to start
beta). It looks like this:
create table person (id integer, name varchar);
insert into person values(1,'Bob');
insert into person values(2,'Sue');
create table stuff (person_id integer, stuff_name text);
insert into stuff values(1,'chair');
insert into stuff values(1,'couch');
insert into stuff values(1,'lamp');
insert into stuff values(2,'table');
insert into stuff values(2,'shirt');
create or replace view person_with_stuff as select p.id as id, p.name as
name, ARRAY(select s.stuff_name from stuff s where s.person_id = p.id)
as stuff from person p;
regression=# select * from person_with_stuff;
id | name | stuff
+--+
1 | Bob | {chair,couch,lamp}
2 | Sue | {table,shirt}
(2 rows)
HTH,
Joe
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] casting to arrays
Thank you! This is great news. Is there a projected release date for 7.4?
Also, is there a published roadmap, or should I just get on the developers
list?
Thanks again.
---
Mike Rylander
On Friday 18 July 2003 05:34 pm, Joe Conway wrote:
> Mike Rylander wrote:
> > I have a rather odd table structure that I would like to simplify to be a
> > view (for some definition of simplify). The current idea I have is to
> > shovel values from multiple rows in one table into an array in the view.
> > The tables look something like this:
>
>
>
> > Is anything like this possible? I know this may not be good form, but
> > unfortunately (or perhaps fortunately, since it means I have a job) there
> > are business reasons for this, supporting old apps and such.
>
> Not possible in current releases, but it will be in 7.4 (about to start
> beta). It looks like this:
>
> create table person (id integer, name varchar);
> insert into person values(1,'Bob');
> insert into person values(2,'Sue');
>
> create table stuff (person_id integer, stuff_name text);
> insert into stuff values(1,'chair');
> insert into stuff values(1,'couch');
> insert into stuff values(1,'lamp');
> insert into stuff values(2,'table');
> insert into stuff values(2,'shirt');
>
> create or replace view person_with_stuff as select p.id as id, p.name as
> name, ARRAY(select s.stuff_name from stuff s where s.person_id = p.id)
> as stuff from person p;
>
> regression=# select * from person_with_stuff;
> id | name | stuff
> +--+
>1 | Bob | {chair,couch,lamp}
>2 | Sue | {table,shirt}
> (2 rows)
>
> HTH,
>
> Joe
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] [GENERAL] ODBC query problem AGAIN
Ok, thanks. I've applied changes to the source, recompiled and tested, everything is working now. Thanks a lot. Regards. On Fri, 2003-07-18 at 16:50, Maksim Likharev wrote: > Try to EXPLAIN SELECT ..., if it crash you most likely have to recompile > postgres with > that strxfrm fix and it's have nothing to do with your data. > > Basically in my case, SunOS 5.8 ( dunno what Solaris version is that, > probably 8 ) > PG was crashing during cost calculation, long before any data access. > > > > -Original Message- > From: Luis Magaña [mailto:[EMAIL PROTECTED] > Sent: Friday, July 18, 2003 2:43 PM > To: Maksim Likharev > Cc: Postgresql General Mail List > Subject: ODBC query problem AGAIN > > > Hi, > > After having moved all of the data to a new database initializaed with > es_MX as locale, the postmaster is dying and restarting every time a > program tries to read information on this tables: > > pg_catalog.pg_class > pg_catalog.pg_namespace > > it is important to note that if I do a simple select * from table with > either one of them server do not crash at all, the problem seems to > happen only when joining. > > Another important point is that my problems started after I ran vacuumdb > -z on the database, or at least that's what I think. > > Have tried this with psql, dbvisualizer and ODBC driver in windows. > > One of the crashing queries is: > > SELECT > n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attl > en,a.attnum,def.adsrc,dsc.description FROM pg_catalog.pg_namespace n > JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid) JOIN > pg_catalog.pg_attribute a ON (a.attrelid=c.oid) LEFT JOIN > pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = > def.adnum) LEFT JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid > AND a.attnum = dsc.objsubid) LEFT JOIN pg_catalog.pg_class dc ON > (dc.oid=dsc.classoid AND dc.relname='pg_class') LEFT JOIN > pg_catalog.pg_namespace dn ON (dc.relnamespace=dn.oid AND > dn.nspname='pg_catalog') WHERE a.attnum > 0 AND NOT a.attisdropped AND > n.nspname LIKE 'public' AND c.relname LIKE 'catalogo_empaque' AND > a.attname LIKE '%' ORDER BY nspname,relname,attname > > The Log Output after that query is: > > 2003-07-18 16:34:59 [9127] LOG: server process (pid 9131) was > terminated by signal 10 > 2003-07-18 16:34:59 [9127] LOG: terminating any other active server > processes > 2003-07-18 16:34:59 [9127] LOG: all server processes terminated; > reinitializing shared memory and semaphores > 2003-07-18 16:34:59 [9134] LOG: database system was interrupted at > 2003-07-18 16:32:58 CDT > 2003-07-18 16:34:59 [9134] LOG: checkpoint record is at 0/2F4DAC30 > 2003-07-18 16:34:59 [9134] LOG: redo record is at 0/2F4DAC30; undo > record is at 0/0; shutdown TRUE > 2003-07-18 16:34:59 [9134] LOG: next transaction id: 8808; next oid: > 1833525 > 2003-07-18 16:34:59 [9134] LOG: database system was not properly shut > down; automatic recovery in progress > 2003-07-18 16:34:59 [9134] LOG: ReadRecord: record with zero length > at 0/2F4DAC70 > 2003-07-18 16:34:59 [9134] LOG: redo is not required > 2003-07-18 16:35:02 [9134] LOG: database system is ready > > Plataform is Solaris 8, running on Sparc compiled with 32bit only, no > core is dumped by the crashing processes. > > Any new suggestions ?, I will try locale 'C' when moving data to new > database. > > Regards. -- Luis Magaña. Gnovus Networks & Software. www.gnovus.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] casting to arrays
Mike Rylander wrote: Thank you! This is great news. Is there a projected release date for 7.4? Not exactly an officially projected date, but in the past IIRC beta/RC has lasted 2 to 3 months, so I'd start looking for a 7.4 release in October. Also, is there a published roadmap, or should I just get on the developers list? The closest thing is the TODO list: http://developer.postgresql.org/todo.php But if you want to closely monitor the work actually getting done, subscribe to the HACKERS list. Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
