[SQL] CREATE TABLE AS and tablespaces
Hello, Recently, I wanted to create a table from a complex query in a specific tablespace, but CREATE TABLE name AS SELECT ... does not accept a tablespace argument. I worked around it by CREATE TABLE name AS SELECT ... LIMIT 0, then moving the generated table to the other tablespace using ALTER TABLE, and then using INSERT INTO ... SELECT to generate the data into the table. But nevertheless, I'd like to ask here whether there are specific reasons for omitting the tablespace argument from the CREATE TABLE AS statement. If not, I'd like to request this minor feature :-) Maybe it is even possible to further unify CREATE TABLE and CREATE TABLE AS. Thanks, Schabi -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] "Installing" dbLink
Hi When I execute the query: SELECT *FROM dblink('dbname=Bas','SELECT id FROM person') AS t(a int)WHERE t.a > 9000 AND T.a < 9050; I receive the error message ERROR: function dblink("unknown", "unknown") does not exist How do I install or setup the function dbLink so that I can use this feature. Regards Richard
Win a BlackBerry device from O2 with Yahoo!. Enter now.
Re: [SQL] "Installing" dbLink
am 24.02.2006, um 11:48:44 + mailte Richard C folgendes:
> Hi
>
> When I execute the query:
>
> SELECT *
> FROM dblink('dbname=Bas','SELECT id FROM person') AS t(a int)
> WHERE t.a > 9000 AND T.a < 9050;
>
> I receive the error message
>
> ERROR: function dblink("unknown", "unknown") does not exist
>
> How do I install or setup the function dbLink so that I can use this
> feature.
You should read the docu.
Which distribution do you have? You should install the contrib-package
and then you should run the dblink.sql.
psql your_database < dblink.sql
After this, you can use dblink.
HTH, Andreas
--
Andreas Kretschmer(Kontakt: siehe Header)
Heynitz: 035242/47215, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
===Schollglas Unternehmensgruppe===
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[SQL] After Trigger assignment to NEW
Hi, i am currently on 7.4.12, and i what i try to do is having an AFTER row trigger nullify one NEW column, so as to prevent a subsequent (AFTER row) trigger from using this column's data. Unfortunately this nullification (assignment) does not have any effect on the next AFTER trigger. The first trigger is in pl/pgsql, and the second is in C. One possible use of the above (if it worked) would be the following. Suppose the next AFTER triger is the (enhanced version of) dbmirror trigger which takes care of FK constraints and navigates thru the graph in order to mirror all depenent tables' rows too. Suppose there is no need to mirror a specific parent table. Then in the child table i write an AFTER trigger that nullifies this columns which is FK to the said parent table, and so prevent the unwanted traversal from happening in the execution of the next AFTER trigger (dbmirror). I'd like to ask, if it is considered the right behaviour and if there is a plan in changing it. Is there a reason that the NEW values should remain unchanged in AFTER row triggers? -- -Achilleus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] After Trigger assignment to NEW
Achilleus Mantzios <[EMAIL PROTECTED]> writes: > Is there a reason that the NEW values should remain unchanged in AFTER > row triggers? By definition, an AFTER trigger is too late to change what was stored. Use a BEFORE trigger. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] After Trigger assignment to NEW
Tom Lane wrote: > Achilleus Mantzios <[EMAIL PROTECTED]> writes: > > Is there a reason that the NEW values should remain unchanged in AFTER > > row triggers? > > By definition, an AFTER trigger is too late to change what was stored. > Use a BEFORE trigger. But a BEFORE trigger would alter the stored tuple, which is not what Achilleus wants AFAIU. I think the desired effect can be had by having DBMirror check the source relation of the inserted tuple (There is a hidden attributa called tableoid IIRC that can be used for that, I think). -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] After Trigger assignment to NEW
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Achilleus Mantzios <[EMAIL PROTECTED]> writes: >>> Is there a reason that the NEW values should remain unchanged in AFTER >>> row triggers? >> >> By definition, an AFTER trigger is too late to change what was stored. >> Use a BEFORE trigger. > But a BEFORE trigger would alter the stored tuple, which is not what > Achilleus wants AFAIU. Oh, I misunderstood what he wanted ... and now that I do understand, I think it's a really terrible idea :-(. A large part of the point of using an AFTER trigger is to be certain you know exactly what got stored. (BEFORE triggers can never know this with certainty because there might be another BEFORE trigger that runs after them and edits the tuple some more.) If one AFTER trigger could falsify the data seen by the next, then that guarantee crumbles. For instance, a minor programming error in a user-written trigger could break foreign-key checking. No thanks. > I think the desired effect can be had by having DBMirror check the > source relation of the inserted tuple (There is a hidden attributa > called tableoid IIRC that can be used for that, I think). I agree --- the correct solution is to change the DBMirror triggers to incorporate the desired filtering logic. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Missing fields on Query result.
Hi Guys, I have another question, but in this time is regarding to a query that supose to return son char fields, but they don't show up, I'm using ODBC Driver 7.03.02.00 with Delphi 6.Thanks in advanced, and regards,Daniel Hernández.Tijuana, BC, México."More you learn, more you earn".Join Excite! - http://www.excite.comThe most personalized portal on the Web!
Re: [SQL] Missing fields on Query result.
Never Mind, it's solved now,Thanx any way, regards,Daniel Hernández.Tijuana, BC, México."More you learn, more you earn". --- On Fri 02/24, Daniel Hernandez < [EMAIL PROTECTED] > wrote:From: Daniel Hernandez [mailto: [EMAIL PROTECTED]To: pgsql-sql@postgresql.orgDate: Fri, 24 Feb 2006 13:08:51 -0500 (EST)Subject: [SQL] Missing fields on Query result. Hi Guys, I have another question, but in this time is regarding to a query that supose to return son char fields, but they don't show up, I'm using ODBC Driver 7.03.02.00 with Delphi 6.Thanks in advanced, and regards,Daniel Hernández.Tijuana, BC, México."More you learn, more you earn".Join Excite! - http://www.excite.comThe most personalized portal on the Web!Join Excite! - http://www.excite.comThe most personalized portal on the Web!
Re: [SQL] After Trigger assignment to NEW
O Tom Lane έγραψε στις Feb 24, 2006 : > Achilleus Mantzios <[EMAIL PROTECTED]> writes: > > Is there a reason that the NEW values should remain unchanged in AFTER > > row triggers? > > By definition, an AFTER trigger is too late to change what was stored. > Use a BEFORE trigger. Too late if someone wants to store it. I wanna store the intented original values, thats why i use AFTER trigger. But i would like to alter what a final AFTER trigger would see. I'll elabarote a little. An update happens. The row is stored. An after trigger is fired that alters some NEW columns (nullifies them), aiming for a subsequent trigger to see the altered results . It should be something like a pointer to a HeapTuple, (right?), so that would be feasible i suppose. I would not even make a post if it was something that trivial. I hope you get my point. > > regards, tom lane > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to >choose an index scan if your joining column's datatypes do not >match > -- -Achilleus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] After Trigger assignment to NEW
O Tom Lane έγραψε στις Feb 24, 2006 : > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >> Achilleus Mantzios <[EMAIL PROTECTED]> writes: > >>> Is there a reason that the NEW values should remain unchanged in AFTER > >>> row triggers? > >> > >> By definition, an AFTER trigger is too late to change what was stored. > >> Use a BEFORE trigger. > > > But a BEFORE trigger would alter the stored tuple, which is not what > > Achilleus wants AFAIU. > > Oh, I misunderstood what he wanted ... and now that I do understand, > I think it's a really terrible idea :-(. A large part of the point > of using an AFTER trigger is to be certain you know exactly what got > stored. (BEFORE triggers can never know this with certainty because > there might be another BEFORE trigger that runs after them and edits the > tuple some more.) If one AFTER trigger could falsify the data seen by > the next, then that guarantee crumbles. For instance, a minor > programming error in a user-written trigger could break foreign-key > checking. No thanks. Alvaro, Tom, thanx a lot, i'll have to incorporate that into dbmirror. > > > I think the desired effect can be had by having DBMirror check the > > source relation of the inserted tuple (There is a hidden attributa > > called tableoid IIRC that can be used for that, I think). > > I agree --- the correct solution is to change the DBMirror triggers to > incorporate the desired filtering logic. > > regards, tom lane > -- -Achilleus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] After Trigger assignment to NEW
Achilleus Mantzios wrote: > O Tom Lane έγραψε στις Feb 24, 2006 : > > > By definition, an AFTER trigger is too late to change what was > > stored. Use a BEFORE trigger. > > Too late if someone wants to store it. > I wanna store the intented original values, thats why i use > AFTER trigger. > But i would like to alter what a final AFTER trigger would see. > > I'll elabarote a little. > > An update happens. > The row is stored. > An after trigger is fired that alters some NEW columns > (nullifies them), aiming for a subsequent trigger > to see the altered results . > > It should be something like a pointer to a HeapTuple, (right?), > so that would be feasible i suppose. > > I would not even make a post if it was something that trivial. > > I hope you get my point. Your real problem is that the "subsequent" trigger has behaviour you don't like. That's what you should be fixing. If dbmirror has no way to exclude specific tables from mirroring, take it up with them as a feature request, or patch dbmirror to work how you want it to. AFTER triggers *must* receive the row that was actually inserted/updated/deleted. If they could receive a "modified" row that didn't reflect what was actually in the database, all sorts of useful trigger-based logging and replication patterns wouldn't work, and there's really no other way to implement them. See also Tom Lane's other message for further implications of being able to modify the rows seen by AFTER triggers. I'd also be hesitant to write triggers that have to execute in a specific order. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Can't connect to the db
Hello every one I was using my db with an application in perl, but something occurs with the application and the pc got frozen and I had to restart my PC, when it boot again I coudn't restart the psql service when I do the psql mydb it shows me the next error, the error is in spanish but it says something like: psql: can't connect to the server: there is no file or directory is the server running local and accepting conecctions in the Unix domain socket «/tmp/.s.PGSQL.5432» do I have to reinstall again the postgres or can I do something else? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Can't connect to the db
Looks like your database didn't start up. You should look at your server logs to see why. A On Fri, Feb 24, 2006 at 04:17:14PM -0600, Judith wrote: > Hello every one I was using my db with an application in perl, but > something occurs with the application and the pc got frozen and I had > to restart my PC, when it boot again I coudn't restart the psql service > when I do the psql mydb it shows me the next error, the error is in > spanish but it says something like: > >psql: can't connect to the server: there is no file or directory > >is the server running local and accepting conecctions in the Unix > domain socket «/tmp/.s.PGSQL.5432» > > do I have to reinstall again the postgres or can I do something else? > > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Obtaining client IP address
Hi, I need to have a db table containing all connected clients (of a distributed application) and among other info, their IP. If both the client and the server are on the same LAN, no problem, the client can supply it's own info. But if they are connected via some sort of bridge/router/NAT? For instance, a client on a LAN connects to a server over the Internet? The client has no idea what public IP it has. The server ought to know... however I haven't found how I could get this info into my query. What I need is some sort of procedure whereby the client enrolls onto the system and supplies the info... I hope I made my self clear. If not I can try and be more specific! Alex. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Obtaining client IP address
On Sat, Feb 25, 2006 at 12:16:31AM +0100, Alexis Paul Bertolini wrote: > The client has no idea what public IP it has. The server ought to > know... however I haven't found how I could get this info into my query. What version of PostgreSQL are you running? In 8.0 and later you can use inet_client_addr(). http://www.postgresql.org/docs/8.1/interactive/functions-info.html I forget if earlier versions have a way to get the client's IP address without resorting to hacks (e.g., writing a function in a privileged language like plperlu and calling netstat or lsof). -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] CREATE TABLE AS and tablespaces
Markus Schaber wrote: > Hello, > > Recently, I wanted to create a table from a complex query in a specific > tablespace, but CREATE TABLE name AS SELECT ... does not accept a > tablespace argument. > > I worked around it by CREATE TABLE name AS SELECT ... LIMIT 0, then > moving the generated table to the other tablespace using ALTER TABLE, > and then using INSERT INTO ... SELECT to generate the data into the table. > > But nevertheless, I'd like to ask here whether there are specific > reasons for omitting the tablespace argument from the CREATE TABLE AS > statement. If not, I'd like to request this minor feature :-) > > Maybe it is even possible to further unify CREATE TABLE and CREATE TABLE AS. This feature will be in 8.2: revision 2.530 date: 2006/02/19 00:04:27; author: neilc; state: Exp; lines: +16 -20 Add TABLESPACE and ON COMMIT clauses to CREATE TABLE AS. ON COMMIT is required by the SQL standard, and TABLESPACE is useful functionality. Patch from Kris Jurka, minor editorialization by Neil Conway. -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] After Trigger assignment to NEW
O Owen Jacobson έγραψε στις Feb 24, 2006 : > Achilleus Mantzios wrote: > > > O Tom Lane έγραψε στις Feb 24, 2006 : > > > > > By definition, an AFTER trigger is too late to change what was > > > stored. Use a BEFORE trigger. > > > > Too late if someone wants to store it. > > I wanna store the intented original values, thats why i use > > AFTER trigger. > > But i would like to alter what a final AFTER trigger would see. > > > > I'll elabarote a little. > > > > An update happens. > > The row is stored. > > An after trigger is fired that alters some NEW columns > > (nullifies them), aiming for a subsequent trigger > > to see the altered results . > > > > It should be something like a pointer to a HeapTuple, (right?), > > so that would be feasible i suppose. > > > > I would not even make a post if it was something that trivial. > > > > I hope you get my point. > > Your real problem is that the "subsequent" trigger has behaviour you don't > like. That's what you should be fixing. If dbmirror has no way to exclude > specific tables from mirroring, take it up with them as a feature request, or > patch dbmirror to work how you want it to. > > AFTER triggers *must* receive the row that was actually > inserted/updated/deleted. If they could receive a "modified" row that didn't > reflect what was actually in the database, all sorts of useful trigger-based > logging and replication patterns wouldn't work, and there's really no other > way to implement them. See also Tom Lane's other message for further > implications of being able to modify the rows seen by AFTER triggers. > As i have explained my dbmirror is FK null values gnostic(=aware) already as we speak. It normaly mirrors father rows according to certain criteria. (And the fathers of them and so on). Replication is done over UUCP over 5$/min satelite connections, so replicating just the right data for a slave is critically important. So nullifying a value just before the dbmirror trigger would do exactly the right thing (for me) Now implementing the "nullification on demand" feature in dbmirror means more work when i migrate to 8.x, i have severly modified dbmirror to do many things, and i thought it was time to stop! > I'd also be hesitant to write triggers that have to execute in a specific > order. Meaning that would hurt portability? Most people need features rathen than the relief to know they can migrate to another database (which they probably never will) > Back to AFTER trigger changing values issue, i think things are not so dramatic if FK triggers could just be fired first. Anyway i'll modify dbmirror again. Oh BTW, There is a patch for DBMirror.pl (which steven hasnt yet fully reviewed) that solves the previous performance problems. > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings > -- -Achilleus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Relation 0 does not exist
Hi. When I run SQL command, I've got the error messe 'Relation 0 doesn't exist' and following log messages were generated. After this error happens, I can not access database at all. Could you kindly advise why this kind of error happens? Regards, y.furuse --- [Version] PosrgreSQL7.2.4 [SQL command] INSERT INTO SDBA2 SELECT * FROM VHIST2 ; [TABLE Layout] CREATE TABLE SDBA2( Client TEXT, RESVNO TEXT, DEPTTEXT, CUSTID TEXT, DT DATE, TM INT, DTM TEXT, RESVTM TIMESTAMP, CPLTTM TIMESTAMP, WTM INTERVAL, STM INTERVAL, OBJ TEXT, METHOD TEXT, COMTM TEXT, DIAGTEXT, TREAT TEXT, NEXTTEXT, AG TEXT, AGDAYS INT, AGDTDATE, FU TEXT, FUDAYS INT, FUDTDATE, PS TEXT, PSDAYS INT, PSDTDATE, STS TEXT, COMMNT TEXT, AGE INT, SEX TEXT, OCCUP TEXT, ITYPTEXT, ATT1TEXT, PRIMARY KEY(CLIENT,RESVNO) ); [LOG message] Feb 25 02:30:01 postgres[736]: [1] DEBUG: server process (pid 32278) was terminated by signal 11 Feb 25 02:30:01 postgres[736]: [2] DEBUG: terminating any other active server processes Feb 25 02:30:01 postgres[736]: [3] DEBUG: all server processes terminated; reinitializing shared memory and semaphores Feb 25 02:30:01 postgres[32289]: [4] DEBUG: database system was interrupted at 2006-02-25 01:52:52 JST Feb 25 02:30:01 postgres[32289]: [5] DEBUG: checkpoint record is at 1/1DE7CE8C Feb 25 02:30:01 postgres[32289]: [6] DEBUG: redo record is at 1/1DE7CE8C; undo record is at 0/0; shutdown FALSE Feb 25 02:30:01 postgres[32289]: [7] DEBUG: next transaction id: 6670787; next oid: 117726 Feb 25 02:30:01 postgres[32289]: [8] DEBUG: database system was not properly shut down; automatic recovery in progress Feb 25 02:30:01 postgres[32289]: [9] DEBUG: ReadRecord: record with zero length at 1/1DE7CECC Feb 25 02:30:01 postgres[32289]: [10] DEBUG: redo is not required Feb 25 02:30:11 postgres[32289]: [11] DEBUG: database system is ready Feb 25 03:00:00 postgres[1006]: [4] ERROR: cannot open segment 1 of relation pg_statistic_relid_att_index (target block 858666545): No such file or directory Feb 25 03:30:00 postgres[2452]: [4] ERROR: cannot open segment 1 of relation pg_statistic_relid_att_index (target block 858666545): No such file or directory Feb 25 04:00:00 postgres[3896]: [4] ERROR: cannot open segment 1 of relation pg_statistic_relid_att_index (target block 858666545): No such file or directory ---(end of broadcast)--- TIP 6: explain analyze is your friend
