Re: [SQL] RE: pl/pgsql and returning rows
From: "Bruce Momjian" <[EMAIL PROTECTED]> > MY book in chapter 18 has a Pl/PgSQL function called change_statename > that does insert/update automatically. > > http://www.postgresql.org/docs/awbook.html The functions called get_details though, so I assumed it's supposed to be shorthand for a join. - Richard > > As a workaround, you can insert your row into an existing table, then > > retrieve it from there later. I think you need to enumerate all of the > > fields, as in 'INSERT INTO table VALUES (ret.field1, > > ret.field2,...ret.fieldn);'. At least, I haven't succeeded any other way. > > Messy, but the best method available right now. > > > > > -Original Message- > > > From: Richard Huxton [SMTP:[EMAIL PROTECTED]] > > > Sent: Tuesday, March 27, 2001 2:27 AM > > > To: [EMAIL PROTECTED]; wade > > > Subject: Re: pl/pgsql and returning rows > > > > > > From: "wade" <[EMAIL PROTECTED]> > > > > > > > create function get_details(int4) returns details as ' > > > > declare > > > > ret details%ROWTYPE; > > > > site_recrecord; > > > > cntct contacts%ROWTYPE; > > > > begin > > > > select into site_rec * sites_table where id = $1 limit 1; > > > > select into cntct * from contacts where id = site_rec.contact; > > > > > > > > -- and then i populate rows of ret. > > > > ret.name := cntct.name; > > > > ret.ip := site_rec.ip; > > > > . > > > > . > > > > . > > > > return ret; > > > > end; > > > > ' language 'plpgsql'; > > > > > > > > now the problem is when is when I do a: > > > > SELECT get_details(55); > > > > all i get is a single oid-looking return value: > > > > get_details > > > > - > > > > 136295592 > > > > (1 row) > > > > > > Sorry - you can't return a row from a function at the present time (except > > > for trigger functions which are special) although I believe this is on the > > > todo list for a later 7.x release. > > > > > > Just from the top of my head, you might try a view with a select rule, > > > although I'm not completely clear what your objectives are. > > > > > > - Richard Huxton > > > > > > > > > ---(end of broadcast)--- > > > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > > > > ---(end of broadcast)--- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > > > > > -- > Bruce Momjian| http://candle.pha.pa.us > [EMAIL PROTECTED] | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] DELETE FROM fails with error
Hi folks,
I have the problem that I can't delete datasets out of my tables. It's like that:
I have a table:
tblshop
ID_Shop oid with sequence
---
Sh_Name
ID_Country
...
there is an index on ID_Country
I have a second table:
tblcountry
ID_Country oid with sequence
--
C_Name
...
I have a reference between these two tables pointing from tblshop.ID_Country to
tblcountry.ID_Country
When I try to delete a row from tblshop I get the error that postgres can't find
the attribute id_shop. My SQL-command looks like follows:
DELETE FROM tblshop WHERE tblshop."ID_Shop" = 12
the same happens when I try to do:
DELETE FROM tblshop WHERE tblshop."ID_Country" = 3
I also tried this query without quotes, with simple quotes (') without the leading
tablename and so on, but I always get the error-message:
ERROR: Attribute 'id_shop' not found
Please, can anyone help me out? It is really anoying when you can't delete datasets
especially because my application is already online (I use postgres with PHP) and
there are 20 tables with alltogether 120 MB of data in it
chris
--
+-+
| chris |
| Database Developer |
| uscreen GmbH|
| |
| --- |
| eMail [EMAIL PROTECTED] |
| Fon (02 02) 24 49 88 - 23 |
+-+
---(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
[SQL] Killing Postmaster
Hi All, What is the correct way of killing postgres 7.0 on redhat linux. Is there a reason why vacuum hangs on a DB with about 1.5 million rows? Cheers Graham winmail.dat ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Killing Postmaster
On Wed, 28 Mar 2001, Graham Vickrage wrote: > Hi All, > > What is the correct way of killing postgres 7.0 on redhat linux. "man pg_ctl". > > Is there a reason why vacuum hangs on a DB with about 1.5 million rows? Vacuum can take a long time with 1.5M records. How long have you waited? Is there any error message? Poul L. Christiansen ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Killing Postmaster
A week? That sounds much too long to me (assuming that you have PostgresSQL funning on fairly good hardware). There could be something wrong with at specific table. Try to vacuum a table one at the time using "vacuum verbose MyTable", and report any error you may find. I don't understand the output of a vacuum verbose, but I'm sure other people on this list do :-) Poul L. Christiansen On Wed, 28 Mar 2001, A James Lewis wrote: > > I have a vacuum which has been running for about a week, is that too long? > > On Wed, 28 Mar 2001, Poul L. Christiansen wrote: > > > On Wed, 28 Mar 2001, Graham Vickrage wrote: > > > > > Hi All, > > > > > > What is the correct way of killing postgres 7.0 on redhat linux. > > > > "man pg_ctl". > > > > > > > > Is there a reason why vacuum hangs on a DB with about 1.5 million rows? > > > > Vacuum can take a long time with 1.5M records. How long have you > > waited? Is there any error message? > > > > Poul L. Christiansen > > > > > > ---(end of broadcast)--- > > TIP 4: Don't 'kill -9' the postmaster > > > > A. James Lewis ([EMAIL PROTECTED]) > If your OS needs a virus detector... RUN!!! > ...Out and get Linux! > ---(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
[SQL] counting distinct rows on more than one column
Hi, on 7.0.3 want to COUNT SELECT DISTINCT a,b FROM t; I can't find a solution because any combination with count with more than one column gives syntax errors. One solution would be to set a view: CREATE VIEW v AS SELECT DISTINCT a,b FROM t; and then SELECT count(a) FROM v but views don't support distinct in v7.0.3 Ok I could use a temporary table but my select distinct tends to give large results. Any clues? Dirk ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
RE: [SQL] counting distinct rows on more than one column
Title: RE: [SQL] counting distinct rows on more than one column SELECT count(*) FROM (SELECT DISTINCT a, b FROM t) AS t2; should give you what you want. MikeA >> -Original Message- >> From: Dirk Lutzebaeck [mailto:[EMAIL PROTECTED]] >> Sent: 28 March 2001 16:11 >> To: [EMAIL PROTECTED] >> Subject: [SQL] counting distinct rows on more than one column >> >> >> >> Hi, >> >> on 7.0.3 want to COUNT >> >> SELECT DISTINCT a,b FROM t; >> >> I can't find a solution because any combination with >> count with more than one column gives syntax errors. >> >> One solution would be to set a view: >> >> CREATE VIEW v AS SELECT DISTINCT a,b FROM t; >> >> and then >> >> SELECT count(a) FROM v >> >> but views don't support distinct in v7.0.3 >> >> Ok I could use a temporary table but my select distinct tends to give >> large results. >> >> Any clues? >> >> Dirk >> >> >> >> ---(end of >> broadcast)--- >> TIP 1: subscribe and unsubscribe commands go to >> [EMAIL PROTECTED] >> _ This e-mail and any attachments are confidential and may also be privileged and/or copyright material of Intec Telecom Systems PLC (or its affiliated companies). If you are not an intended or authorised recipient of this e-mail or have received it in error, please delete it immediately and notify the sender by e-mail. In such a case, reading, reproducing, printing or further dissemination of this e-mail is strictly prohibited and may be unlawful. Intec Telecom Systems PLC. does not represent or warrant that an attachment hereto is free from computer viruses or other defects. The opinions expressed in this e-mail and any attachments may be those of the author and are not necessarily those of Intec Telecom Systems PLC. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. __
Re: [SQL] DELETE FROM fails with error
Chris,
> I have a reference between these two tables pointing from
> tblshop.ID_Country to
> tblcountry.ID_Country
>
> When I try to delete a row from tblshop I get the error that postgres
> can't find
> the attribute id_shop. My SQL-command looks like follows:
>
> DELETE FROM tblshop WHERE tblshop."ID_Shop" = 12
>
> the same happens when I try to do:
>
> DELETE FROM tblshop WHERE tblshop."ID_Country" = 3
>
> I also tried this query without quotes, with simple quotes (')
> without the leading
> tablename and so on, but I always get the error-message:
Two possibilities:
1. You're using a couple of different cases in the above example.
PostgreSQL is case-sensitive. What interface program are you using?
What OS? From the error messages, it looks like your commands are being
lower-cased by the interface or data transport, which would cause
Postgres not to recognize the field names.
a. Try you commands from PSQL on the Database server, making sure that
your case is the same as the table definition.
b. If you can down the DB for an hour, try re-naming one of the ID
fields in lower case (one that isn't foriegn keyed, of course).
2. It's possible that defining these rows as type OID requires some
special reference syntax. If so, hopefully someone on the list will
come forward.
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law firms, small businessesfax 621-2533
and non-profit organizations. San Francisco
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] counting distinct rows on more than one column
In 7.0.3, I believe the following would work: SELECT count(distinct(a || b)) FROM t; if subselects in from were supported in 7.0.3 as they are in 7.1, you could do: SELECT count(*) FROM (SELECT DISTINCT a,b FROM t) FROM x Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio On Wed, 28 Mar 2001, Dirk Lutzebaeck wrote: > > Hi, > > on 7.0.3 want to COUNT > > SELECT DISTINCT a,b FROM t; > > I can't find a solution because any combination with > count with more than one column gives syntax errors. > > One solution would be to set a view: > > CREATE VIEW v AS SELECT DISTINCT a,b FROM t; > > and then > > SELECT count(a) FROM v > > but views don't support distinct in v7.0.3 > > Ok I could use a temporary table but my select distinct tends to give > large results. > > Any clues? > > Dirk > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] DELETE FROM fails with error
chris Günther <[EMAIL PROTECTED]> writes: > When I try to delete a row from tblshop I get the error that postgres > can't find the attribute id_shop. My SQL-command looks like follows: > DELETE FROM tblshop WHERE tblshop."ID_Shop" = 12 That looks like the correct way of quoting a mixed-case field name. I think the field is not named quite like you think it is. Try doing pg_dump -s -t tblshop databasename to see what the field names really are. regards, tom lane ---(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] counting distinct rows on more than one column
Dirk Lutzebaeck <[EMAIL PROTECTED]> writes: > on 7.0.3 want to COUNT > SELECT DISTINCT a,b FROM t; In 7.1 you could do select count(*) from (select distinct a,b from t) as t1; In 7.0 and before I think you have no choice but to use a temp table. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Function with now() | time 'now' | etc...
Hi all,
I wrote the folling function:
DROP FUNCTION PROC_TESTE(INTEGER);
CREATE FUNCTION PROC_TESTE(INTEGER) RETURNS INTEGER AS '
DECLARE
SEQ RECORD;
BEGIN
SELECT NEXTVAL(''TEMPOS_ID_SEQ'') AS ID INTO SEQ;
INSERT INTO TEMPOS (ID, INICIO) VALUES (SEQ.ID, NOW());
FOR I IN 1..$1 LOOP
INSERT INTO TESTE(VALOR) VALUES (RANDOM()*$1);
END LOOP;
UPDATE TEMPOS SET FIM = NOW() WHERE ID = SEQ.ID;
RETURN SEQ.ID;
END;'
LANGUAGE 'PLPGSQL';
And all times I run it ("select proc_teste(1);"), i got the folling
behavour:
teste=> SELECT * FROM TEMPOS;
inicio | fim| id
--+--+
15:12:17 | 15:12:17 | 23
15:12:18 | 15:12:18 | 24
(...)
It doesn't getting different times on each execution. I also tried put
"timestamp 'now'" insted "now()". What am I doing wrong?
Thank you,
Edipo Elder
[[EMAIL PROTECTED]]
_
Oi! Você quer um iG-mail gratuito?
Então clique aqui: http://www.ig.com.br/paginas/assineigmail.html
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Oracle -> Postgresql migration
On Wed, Mar 28, 2001 at 01:24:11PM -0500, Douglas Brunton wrote: > Hello, > I am currently in the process of attempting an Oracle 8i to Postgresql 7.0.3 >migration. The table conversions were pretty straight forward, and I have all of the >tables ported over (with some minor datatype conversions). The stored procedures are >a different case altogether. I am wondering if anyone on the list has >pointers/conversion utilities for undertaking this task. Any information is welcome. I wrote a "Porting from Oracle PL/SQL" document that was added to the PL/pgSQL documentation in 7.1. It has lots of useful info on how to port to PL/pgSQL. You can find this document under the Programmer's manual on the PG 7.1 documentation (development docs). This week I will change that document to become more generic and add information on how to change some Oracle'isms (e.g. nvl, decode, etc.) to PostgreSQL, sticking to the standard where possible. -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club|--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Web Developer Backup not found: (Q)uem mandou usar o Stacker? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Function with now() | time 'now' | etc...
[EMAIL PROTECTED] writes: > It doesn't getting different times on each execution. I also tried put > "timestamp 'now'" insted "now()". What am I doing wrong? now() is defined to return the time of the start of the current transaction. It won't change value inside a transaction. See http://www.postgresql.org/devel-corner/docs/postgres/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] counting distinct rows on more than one column
Michael Fork writes: > In 7.0.3, I believe the following would work: > > SELECT count(distinct(a || b)) FROM t; Great, this works! I don't quite get it why... Dirk ---(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] counting distinct rows on more than one column
Dirk Lutzebaeck <[EMAIL PROTECTED]> writes: > Michael Fork writes: >>> In 7.0.3, I believe the following would work: >>> >>> SELECT count(distinct(a || b)) FROM t; > Great, this works! I don't quite get it why... Michael really should not have proposed that solution without mentioning its limitations: it's not actually counting distinct values of the column pair a,b, but only of their textual concatenation. For example a = 'xy' and b = 'z' will look the same as a = 'x' and b = 'yz'. If there is some character you never use in column A, say '|', you could do count(distinct(a || '|' || b)) with some safety, but this strikes me as still a pretty fragile approach. regards, tom lane ---(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] DELETE FROM fails with error
I was going to guess that it was something related to the
foreign key, but I can't imagine why that would be affected
by a delete on the referencing table (there shouldn't be
a trigger there anyway).
Can you send full schema with constraints for the tables?
On Wed, 28 Mar 2001, chris Günther wrote:
> Hi folks,
>
> I have the problem that I can't delete datasets out of my tables. It's like that:
> I have a table:
> tblshop
> ID_Shop oid with sequence
> ---
> Sh_Name
> ID_Country
> ...
>
> there is an index on ID_Country
>
>
> I have a second table:
> tblcountry
> ID_Country oid with sequence
> --
> C_Name
> ...
>
> I have a reference between these two tables pointing from tblshop.ID_Country to
> tblcountry.ID_Country
>
> When I try to delete a row from tblshop I get the error that postgres can't find
> the attribute id_shop. My SQL-command looks like follows:
>
> DELETE FROM tblshop WHERE tblshop."ID_Shop" = 12
>
> the same happens when I try to do:
>
> DELETE FROM tblshop WHERE tblshop."ID_Country" = 3
>
> I also tried this query without quotes, with simple quotes (') without the leading
> tablename and so on, but I always get the error-message:
>
> ERROR: Attribute 'id_shop' not found
>
> Please, can anyone help me out? It is really anoying when you can't delete datasets
> especially because my application is already online (I use postgres with PHP) and
> there are 20 tables with alltogether 120 MB of data in it
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
[SQL] RE: counting distinct rows on more than one column
I don't think this will necessarily work: field1 | field2 aa | ab a | aab These are two distinct rows, so should be counted as two. The proposed method would count them as one. You can get around this problem by doing: count (distinct (a || x || b)) where x is some character not found in your data. > -Original Message- > From: Dirk Lutzebaeck [SMTP:[EMAIL PROTECTED]] > Sent: Wednesday, March 28, 2001 1:32 PM > To: Michael Fork > Cc: [EMAIL PROTECTED] > Subject: Re: counting distinct rows on more than one column > > Michael Fork writes: > > In 7.0.3, I believe the following would work: > > > > SELECT count(distinct(a || b)) FROM t; > > Great, this works! I don't quite get it why... > > Dirk > > ---(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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] RE: counting distinct rows on more than one column
If the fields are fixed length character type, then the simpler concatenation should work. Actually, the requirement is only that all but the final field be fixed length. And if they aren't fixed length, you can cast them to be such, as long as you know the maximum length of the string values, as in the following where that maximum is 20 and we are looking at 3 fields: select count(distinct (cast(field1 as char(20)) || cast(field2 as char(20)) || field3)) from ... Jim Ballard - Original Message - From: "Jeff Eckermann" <[EMAIL PROTECTED]> To: "'Dirk Lutzebaeck'" <[EMAIL PROTECTED]>; "Michael Fork" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, March 28, 2001 3:43 PM Subject: [SQL] RE: counting distinct rows on more than one column > I don't think this will necessarily work: > > field1 | field2 > aa | ab > a | aab > > These are two distinct rows, so should be counted as two. > The proposed method would count them as one. > You can get around this problem by doing: > count (distinct (a || x || b)) > where x is some character not found in your data. > > > -Original Message- > > From: Dirk Lutzebaeck [SMTP:[EMAIL PROTECTED]] > > Sent: Wednesday, March 28, 2001 1:32 PM > > To: Michael Fork > > Cc: [EMAIL PROTECTED] > > Subject: Re: counting distinct rows on more than one column > > > > Michael Fork writes: > > > In 7.0.3, I believe the following would work: > > > > > > SELECT count(distinct(a || b)) FROM t; > > > > Great, this works! I don't quite get it why... > > > > Dirk > > > > ---(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 > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] SELECT ... FOR UPDATE
I am interested in using the SELECT ... FOR UPDATE feature but it doesn't work quite the way I had hoped. If there is a better/different way of doing this please enlighten me. If I issue: BEGIN; SELECT * FROM atable WHERE atable.key = 10 FOR UPDDATE; in one session and then issue the same commands from a 2nd session, the 2nd session simply waits until the 1st session issues COMMIT or ROLLBACK. While the 2nd session is waiting I am, apparently locked out. What I would like is for the 2nd session to determine if the lock can be obtained and if not, offer the user a choice of waiting or escaping from the SELECT. Is this possible? Using 7.0.3 ---(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
[SQL] Self-Referencing
Hello, I have a feeling this isn't going to make much sense, but I'm gonig to try anyway. What I'd like to do is be able to refer to an outer-SELECT from an inner-SELECT. I hope this makes sense. I need to be able to refer to the row that's being processed in a SELECT. I'm going to use the idea of 'this' referring to the row that's currently being processed. Here's the example of what I'd like: SELECT building_id, num_buildings, ( SELECT count( building_id ) FROM building_portals WHERE building_id = THIS.building_id ) FROM buildings; Am I making things too complicated, and if so will somebody *PLEASE* tell me the easier way to do this. Thanks. -- Dave ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Can a SELECT block?
Hi all. I'm trying to track down a deadlock problem caused by some automatically generated SQL code. It seems I'm deadlocking on a table that is actually quite rarely modified. I'm unsure, but it appears that maybe something is blocking on a SELECT call. Under what situations can a SELECT block? I'm assuming that a simple INSERT probably cannot block anywhere. Under what situations, aside from two UPDATEs to the same row, can an UPDATE block? I'd appreciate any time. To give some details, it appears that my application server (Orion) is holding connections to the database, some of which are not in transactions, and some of which are "primed" for connections (meaning that a BEGIN is always issued, and waits till transactional work needs to be done, at which point it issues a COMMIT and then another BEGIN and waits again). It seems to always issue the command "SET TRANSACTION ISOLATION LEVEL READ COMMITTED" after each BEGIN. I'd appreciate any help that anyone can give. Thanks. Gerald. ---(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] Can a SELECT block?
"Gerald Gutierrez" <[EMAIL PROTECTED]> writes: > Under what situations can a SELECT block? If the table is locked with an exclusive lock. See http://www.postgresql.org/devel-corner/docs/postgres/locking-tables.html > I'd appreciate any time. To give some details, it appears that my > application server (Orion) is holding connections to the database, some of > which are not in transactions, and some of which are "primed" for > connections (meaning that a BEGIN is always issued, and waits till > transactional work needs to be done, at which point it issues a COMMIT and > then another BEGIN and waits again). It seems to always issue the command > "SET TRANSACTION ISOLATION LEVEL READ COMMITTED" after each BEGIN. This would be somewhat annoying for VACUUM, which can only clean out tuples that went away before the oldest open transaction started. But offhand I think it does not matter for anything else. regards, tom lane ---(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
[SQL] 3 options
Hi, Using : PostgreSQL 6.5.3 on i686-pc-linux-gnu Platform : Linux (Red Hat 6.0) 3 errors : 1) pg_dump medias > medias.pgdump280301 pqWait() -- connection not open PQendcopy: resetting connection SQL query to dump the contents of Table 'dossier' did not execute correctly. After we read all the table contents from the backend, PQendcopy() failed. Explanation from backend: 'pqWait() -- connection not open '. The query was: 'COPY "dossier" TO stdout; '. 2) medias=> vacuum; NOTICE: Rel dossier: Uninitialized page 28 - fixing NOTICE: Rel dossier: Uninitialized page 29 - fixing NOTICE: BlowawayRelationBuffers(dossier, 28): block 28 is dirty (private 0, last 0, global 0) pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. We have lost the connection to the backend, so further processing is impossible. Terminating. 3) on my website (using php) : Warning: PostgreSQL query failed: ERROR: Tuple is too big: size 9968 in /xxx/enregistrer3.php on line 45 --- 1 & 2 seem to be ok, because right now i can do a pg_dump without the error, but I've searched in the mailing-list for my question (3) and this is what i came with: --- i have 3 options - which one would you recommand me ? - change the default size block in include/config.h (recompile - hum...) - use large object interface (what is that?) - upgrade to 7.1 (fear to lost my data - i'm not a linux guru) --- thank you patrick, montreal, canada ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Oracle -> Postgresql migration
Hello, I am currently in the process of attempting an Oracle 8i to Postgresql 7.0.3 migration. The table conversions were pretty straight forward, and I have all of the tables ported over (with some minor datatype conversions). The stored procedures are a different case altogether. I am wondering if anyone on the list has pointers/conversion utilities for undertaking this task. Any information is welcome. Douglas BruntonTechnical ImplementationBigfoot Interactive[EMAIL PROTECTED]646.227.7501
