Re: [GENERAL] nested transactions
jprem wrote: hello , postgreqsl 6.5.3 does not support nested transactions. so i don't think it will be suitable for e-com application.isn't it ? does postgreqsl 7.0 support nested transacions ? is postgresql 7.0 a stable version ? Hi, I have around 4000 online shop running with PostgreSQL 6.5.3 , Perl DBI, Apache/mod_perl They are all running well and with transactions Gilles
Re: [GENERAL] Describe structure.
On Tue, 25 Jul 2000, anuj wrote: My table name is tblpg. I want to see the table structure. Like oracle have Describe table name. What PG have ? or any command. Thank in advance. Anuj pg= \d Database= pg +--+--+--+ | Owner | Relation | Type | +--+--+--+ | postgres | tblpg| table| +--+--+--+ It's all in the online help. The command you want is \d table. Brett W. McCoy http://www.chapelperilous.net/~bmccoy/ --- Yow! Is my fallout shelter termite proof?
Re: [GENERAL] Statistical Analysis
Steve Heaven wrote: At 20:18 24/07/00 -0400, you wrote: Sigh, forgot to mention the solution. There's an undocumented function: * bool oidrand (oid o, int4 X)- * takes in an oid and a int4 X, and will return 'true' * about 1/X of the time. typically used like this: -- select roughly 1/10 of the tuples SELECT * FROM onek WHERE oidrand(onek.oid, 10); It doesnt seem to work as you explain. For a value of 1 you expect (nearly) all the tuples and two should return half, but that not what I'm finding. galore= select count(*) from topten where room='HI'; count - 14 (1 row) galore= SELECT * FROM topten WHERE room='HE' and oidrand(topten.oid, 1); type|data |norder|room |grp +-+--+--+--- B |0764552503 | 1|HE| B |0751327190 | 1|HE| B |0718144392 | |HE| B |0500280754 | |HE| B |0028610091 | 1|HE| (5 rows) galore= SELECT * FROM topten WHERE room='HE' and oidrand(topten.oid, 2); type|data |norder|room |grp +-+--+--+--- B |0764552503 | 1|HE| B |0751327190 | 1|HE| B |0718144392 | |HE| (3 rows) galore= SELECT * FROM topten WHERE room='HE' and oidrand(topten.oid, 7); type|data |norder|room |grp +-+--+--+--- B |0751327190 | 1|HE| B |0718144392 | |HE| (2 rows) -- thorNET - Internet Consultancy, Services Training Phone: 01454 854413 Fax: 01454 854412 http://www.thornet.co.uk Isn't it because oidrand evals as 'random() 1/X' ? or maybe 'random() 1/(X+1)' ? -- Guillaume Perréal - Stagiaire MIAG Cemagref (URH), Lyon, France Tél: (+33) 4.72.20.87.64
Re: [GENERAL] Problems upgrading from v6.4.2 to v7.0.2
"Fetter, David M" [EMAIL PROTECTED] writes: Thanks for pointing me in the right direction. I started to look at the db.out file and I noticed the pg_shadow variables. When I looked at the newly built postgres 7.0.2 the usesysid for postgres was the same as the usesysid for sys_request, one of the new accounts I was moving over. Ah, that explains that. Another corner case that pg_dumpall doesn't handle :-(. I think we are planning to eliminate usesysids entirely soon, since they don't seem to have any real function other than creating the risk of this sort of problem... Oh, but one other question to the group...has anyone experienced any issues when importing a database out of and older version and into a newer version? We try to minimize cross-version portability problems, but there are always a few. The release notes for 7.0 should give you more info. regards, tom lane
[GENERAL] I wish to thank...
...the list for inadvertently solving my problem about subscripting. I asked for a suggestion about rephrasing a query with "...peopcode[1,2] = my_var". The obvious solution was "...substr(peopcode,1,2)" Thanks for the unrelated question about updates. Scott
[GENERAL] can disks be read only?
hi, we wish to have a database that is history so we want to make the disks read only after loading. would this cause any problems with POSTGRES? it's on a linux 6.2 box with POSTGRES 7.0.2. what we're looking for here is that if the system crashes we can do a fast boot and not have the system do an FSCK. one company bought another and we're going to warehouse the purchased company's data for reference. they'll be no additions to it once loaded. thanks, mikeo
[GENERAL] Comment #line after pre-processing
Does anyone know if it's possible to suppress the comment ' #line nn ...' which appear in the C file, after pre-processing with ecpg. Thanks
Re: [GENERAL] Comment #line after pre-processing
Michaël Fiey wrote: Does anyone know if it's possible to suppress the comment ' #line nn ...' which appear in the C file, after pre-processing with ecpg. Thanks That's not a comment. It's a mechanism used by cpp (and other preprocessors as well) to tell cc1 (the real C compiler) in which input file and line number it actual is compiling. If you remove this information, compiler error messages cannot tell you anymore the "real" location of the error. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] #
[GENERAL] General Trigger Functions
Hi, Is there a way to write a "generic" trigger function in postgres that can loop thru a table's cloumns (using TG_RELNAME) and perform certain activities based on, say, certain column-types? In other words, is it possible to assign OLD.column_name and NEW.column_name to a variable inside triggers? Thanks, Morey Parang ORNL
RE: [GENERAL] Describe structure.
Wilkinson Charlie E writes: While we're on the topic, can someone tell me where '\d *' went, and what to use instead? That particular command seems to have disappeared in PG7. Hmm, that must have been me. What did it do? Describe all tables at once? -- Peter Eisentraut Sernanders väg 10:115 [EMAIL PROTECTED] 75262 Uppsala http://yi.org/peter-e/Sweden
Re: [GENERAL] ROLLBACK automatically
Chris Bitmead writes: Is this behaviour some kind of standard? Can it be changed? In the past the answer to the question "Does a statement-level error warrant a transaction abort?" has usually hinged on the interpretation of the following clauses of the SQL92 standard. 3.3.4.1 Exceptions The phrase "an exception condition is raised:", followed by the name of a condition, is used in General Rules and elsewhere to indicate that the execution of a statement is unsuccessful, ap- plication of General Rules, other than those of Subclause 12.3, "procedure", and Subclause 20.1, "direct SQL statement", may be terminated, diagnostic information is to be made available, and execution of the statement is to have no effect on SQL-data or schemas. 4.28 SQL-transactions [...] The execution of a rollback statement may be initiated implicitly by an implementation when it detects unrecoverable errors. When such an error occurs, an exception condition is raised: transaction rollback with an implementation-defined subclass code. So essentially we classify all errors as unrecoverable, which is certainly dumb, but legal. But then we still don't comply because we don't execute the rollback automatically but instead hang in some sort of "invalid" state. There is certainly big demand for getting rid of this restriction though, but the code changes could end up being very extensive. -- Peter Eisentraut Sernanders väg 10:115 [EMAIL PROTECTED] 75262 Uppsala http://yi.org/peter-e/Sweden
Re: [GENERAL] Hints about how to debug pg_dump problem?
Philip Warner writes: The read eventually dies with a parse error. I'm sure there must be something wrong in my source database that I'm getting this, but I don't even know where to begin tracking down the problem. I've dumped and re-read this database before, but I've added the data that is causing the problem since then. Obviously, that's what the problem is; I just can't see how the file is getting read. Any hints as to where to start would be much appreciated. The simplest thing to do would be to copy the dump file and edit it; break it into bits that you can run independantly, and see where the error occurs. P.S. I assume you are using the pg_dump that came with 7.0.2 or earlier. If it's 7.0.2 you can run psql with -f (instead of ) and with `-V ON_ERROR_STOP=on' to get error messages with line numbers and immediate exit if an error occurs. -- Peter Eisentraut Sernanders väg 10:115 [EMAIL PROTECTED] 75262 Uppsala http://yi.org/peter-e/Sweden
(Fwd) RE: [GENERAL] PostgreSQL, ODBC, Access (solution for me)
Here's a bothersome issue: I've got the most recent versions of Postgres, ODBC client for Win32, and Access 97. My client can enter new records fine via a linked table. However, when she goes back to add data to a column, she gets the following error: message box title: "Write Conflict" description: "This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made." buttons: "Copy to Clipboard" and "Drop Changes". It appears that *once* Access finds something unique about a record, it uses that to differentiate records. (Check out the SQL log to see) However, a new field in Access has no key *until* PostgreSQL gets it (if you're using a SERIAL field type), and the default values for other fields don't appear either. So, the trick is to have Access deposit a unique value (in this case, a timestamp) into each field. What works for me (even in datasheet view): 1. Create a table w/a timestamp field. CREATE TABLE Foo (id SERIAL PRIMARY KEY, fullname VARCHAR(30) NOT NULL, dt TIMESTAMP DEFAULT 'now' NOT NULL); Then, in Access: Don't use *table* datasheet view. Create a form w/the fields you want, and use that *form*datasheet view. Set it up so that Access has a DefaultValue property of Now() for the "ts" column. (In addition, while you're there, you might want to lock/hide the ts column, and lock the serial column, as Access will let you renumber a PostgreSQL serial field, which I think is a Bad Thing [YMMV].) Then use the datasheet view. Since the "dt" column should be different for each record *from the moment of inception*, this gives Access something really unique to hang its hat on. Works for me; let me know if it doesn't work for you. -- Has anyone ever collected a FAQ of Access-on-Postgresql? I've got a few tips (nothing heavy, just the usual use-float-instead-of- decimal-for-currency), and suspect others have a few. -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
Re: [GENERAL] nested transactions
Gilles DAROLD wrote: jprem wrote: hello , postgreqsl 6.5.3 does not support nested transactions. so i don't think it will be suitable for e-com application.isn't it ? does postgreqsl 7.0 support nested transacions ? is postgresql 7.0 a stable version ? Hi, I have around 4000 online shop running with PostgreSQL 6.5.3 , Perl DBI, Apache/mod_perl They are all running well and with transactions Another interesting fact for you... Weblogic which is basically Tuxedo + CORBA from BEA doesn't support nested transactions either... Since All the transaction stuff comes from tuxedo, tuxedo doesnt support nested transactions. It can suspend a transaction, start and commit another transaction and resume the suspended one, but that'd hardly what I'd call 'nested transactions'. Regards, -ryan -- Ryan Rawson System Administrator Binary Environments Ltd. [EMAIL PROTECTED]
Re: [GENERAL] can disks be read only?
mikeo [EMAIL PROTECTED] writes: we wish to have a database that is history so we want to make the disks read only after loading. would this cause any problems with POSTGRES? In theory you could do it given that you vacuum the tables before locking down the files. (The vacuum is needed to ensure that on-row commit status bits are up to date.) In practice you'd likely have problems with the minor detail that md.c opens everything with O_RDWR privilege requests. You could probably do a quick hack to try O_RDONLY if O_RDWR fails... regards, tom lane
Re: [GENERAL] General Trigger Functions
[EMAIL PROTECTED] wrote: Hi, Is there a way to write a "generic" trigger function in postgres that can loop thru a table's cloumns (using TG_RELNAME) and perform certain activities based on, say, certain column-types? In other words, is it possible to assign OLD.column_name and NEW.column_name to a variable inside triggers? PL/pgSQL has the pseudo type RECORD. And at least it'd be possible with PL/Tcl. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] #
Re: [GENERAL] 4 billion record limit?
Paul Caskey [EMAIL PROTECTED] writes: Tom Lane wrote: It's only a hard limit if your application assumes OIDs are unique. If you don't assume that, then I think it's not a big problem. It's possible (though obviously not especially likely) that you might get OID collisions in the system tables after an OID-counter wraparound. This implies they do wrap around. So they are reused? Chris said no, but you're saying yes. (Maybe they wrap around "by accident", by adding one to MAXINT, which will give zero on an unsigned int, I believe. Will the system choke on zero? Has anyone tested this wraparound?) Yes, yes, and yes ;-). I just committed a patch to prevent the system from generating a zero OID during wraparound. I think that the old code would not have done so in any case, but correct behavior would've depended on the stored OID counter always being a multiple of 32, which seems a tad risky to assume over a long time span. There's a more direct check for wraparound in there now. (No, I didn't test it the hard way ... just stopped the postmaster and hacked on the OID counter in the pg_variable file by hand ...) Even if they do wrap, if I have some old records lying around with a low OIDs, they will trip me up. No doubt about it, you're likely to get a few "duplicate key" errors and stuff like that. I'm just observing that it's not likely to be a complete catastrophe, especially not if you don't rely on OIDs to be unique in your user tables. We have talked about offering 8-byte OIDs as a compile-time option, and I think it'll happen eventually, but I'm not holding my breath. Lots of other stuff seems more pressing... regards, tom lane
[GENERAL] Set valued attributes ?
Does anyone know how to make an attribute a collection of OIDs? I haven't found any reference of this object-oriented feature in Postgres online manuals. Please, let me know any hints/links or anything that could help me doing the following: === Assume that each row of tables is identified by OID. I want to define an attribute, say (t1.c2), as a collection of OIDs linking to rows of table t2. At the same time the attribute (t1.c3) contains a collection of OIDs linking to rows of table t3. === How do I do to make the postgres "navigate through OID links", to prevent expensive access methods (joins, sequential or indexed) ?
