Re: [GENERAL] Query on exception handling in PL/pgSQL

And how does exactly PG8.0 help us with this? Tom, coould you be so kind and tell me/us when will PG incorporate such a hugely claimed feature like "copy table_x from 'file_y.txt' (using delimiters) IGNORE ON DUPLICATES"? I do LOVE PG and I'm very pleased with its overall features level still the

[GENERAL] how many JOINs?

Hello List! Do you know how many times can JOIN be used in a query? How many JOINs does PostgreSQL support in ONE query? ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to

Re: [GENERAL] row-level deadlock problem

On Sat, Nov 27, 2004 at 03:20:16AM +0100, Kamil Kaczkowski wrote: > > Change things so you don't need to update more than one row per query, > > perhaps? The lack of any primary key on that table was already pretty > > disturbing from a database-theory point of view. Maybe you should > > rethink

Re: [GENERAL] Query on exception handling in PL/pgSQL

Am Fr, den 26.11.2004 schrieb diya das um 14:28: > I am just a beginner in postgreSQL and writing some > functions in PL/pgSQL. I use the libpq interface in > the client to call this funtions. My pgsql function > does an insert to a table and I have opened a > transaction block before calling my

[GENERAL] Debian Packages for 8.0

Hi. Are there any debian packages for the 8.0 betas? Thanx, Joachim -- "... ein Geschlecht erfinderischer Zwerge, die fuer alles gemietet werden koennen."- Bertolt Brecht - Leben des Galilei ---(end of broadcast)---

[GENERAL] Could we hide the table name listing from unprivileged user?

Hi, A problem we are facing is although we revoke all from one database user, he can still see the table exists in psql using \dt command, but he can'd select * from it of course, how could we hide the table name listing from him? We are using 7.4.x and 8.0 beta, with ODBC, JDBC and libpq. regard

Re: [GENERAL] row-level deadlock problem

On Sat, 27 Nov 2004, Martijn van Oosterhout wrote: > On Sat, Nov 27, 2004 at 03:20:16AM +0100, Kamil Kaczkowski wrote: > > > Change things so you don't need to update more than one row per query, > > > perhaps? The lack of any primary key on that table was already pretty > > > disturbing from a d

Re: [GENERAL] row-level deadlock problem

On Sat, Nov 27, 2004 at 03:40:11PM +0100, Kamil Kaczkowski wrote: > > It's not the locking on the UPDATE that's getting you. Multiple updates > > can run concurrently (depending on your serialization level anyway, I'm > > talking about default setup here). > > > > Where the problem is is the foreig

Re: [GENERAL] Query on exception handling in PL/pgSQL

On Sat, Nov 27, 2004 at 11:37:34AM +0200, Victor Ciurus wrote: > And how does exactly PG8.0 help us with this? You can use BEGIN ... EXCEPTION ... END blocks. It doesn't help you with COPY/IGNORE ON DUPLICATES though. > Tom, coould you be so kind and tell me/us when will PG incorporate > such a

[GENERAL] Boolean error

Hello, We are experiencing a strange error with the following request : select cmp from (select random () < 0.5 as cmp from pg_type) as tmp where cmp; With this request even false results are shown. This behaviour doesn't appear when we don't join the boolean expression to a table to have mu

[GENERAL] Can one alter the format of a numeric column?

Can one alter the precision of a numeric column? I want to reduce the precision of a column from 20,4 to 20,2. -- Chris Green ([EMAIL PROTECTED]) "Never ascribe to malice, that which can be explained by incompetence." ---(end of broadcast)---

Re: [GENERAL] comp.databases.postgresql.* groups and RFD

Woodchuck Bill <[EMAIL PROTECTED]> wrote in news:[EMAIL PROTECTED]: > "Adam H. Kerman" <[EMAIL PROTECTED]> wrote in > news:pIOdndYMRqGJ7DrcRVn- [EMAIL PROTECTED]: > >> Are these meant to be worldwide Usenet groups >> or newsgroups local to your server? > > Supernews is already carrying all 29 o

Re: [GENERAL] Why the current setup of pgsql.* and comp.databases.postresql.general are BROKEN

Gary L. Burnore <[EMAIL PROTECTED]> wrote in news:[EMAIL PROTECTED]: > I'm posting to a USENet group. I shouldn't be receiving an email from > the list. If the groups had been generated as MODERATED newsgroups, > my post wouldn't hit MY spool, then go to HIS server for some > approval, later to

[GENERAL]

Hello List! Do you know how many times can JOIN be used in a query? How many JOINs does PostgreSQL support? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] comp.databases.postgresql.* groups and RFD

Gary L. Burnore <[EMAIL PROTECTED]> wrote in news:[EMAIL PROTECTED]: >>I just realized what a bad name pgsql.* is for a hierarchy. If someone >>wants to look for a newgroup for PostgreSQL, he will type that >>word/string into his newsreader and it will not bring up any of these >>newsgroups. >

Re: [GENERAL] Can one alter the format of a numeric column?

On Sat, Nov 27, 2004 at 05:29:42PM +, Chris Green wrote: > Can one alter the precision of a numeric column? > > I want to reduce the precision of a column from 20,4 to 20,2. In PostgreSQL 8.0 (currently in beta) you can use ALTER TABLE to alter a column's type. For previous versions see the

Re: [GENERAL] row-level deadlock problem

Martijn van Oosterhout <[EMAIL PROTECTED]> writes: > Well, there has to be something, since an UPDATE in Read Committed mode > simply doesn't have any locks that can deadlock. You're mistaken; it takes a row lock on each row it updates. I'm not sure why the two UPDATEs are visiting the same rows

Re: [GENERAL] how many JOINs?

Konstantin Danilov <[EMAIL PROTECTED]> writes: > Do you know how many times can JOIN be used in a query? How many JOINs does > PostgreSQL support in ONE query? The theoretical upper limit would be around 21000, I think, due to rangetable indexes being 16 bits. Did you need more?

Re: [GENERAL] Could we hide the table name listing from unprivileged user?

Weiping <[EMAIL PROTECTED]> writes: > A problem we are facing is although we revoke all from > one database user, he can still see the table exists > in psql using \dt command, but he can'd select * from it > of course, how could we hide the table name listing from > him? You can't. Put him in a

Re: [GENERAL] Boolean error

Fabien Fournier <[EMAIL PROTECTED]> writes: > We are experiencing a strange error with the following request : > select cmp > from (select random () < 0.5 as cmp from pg_type) as tmp > where cmp; > With this request even false results are shown. I think the planner will flatten this into

Re: [GENERAL] how many JOINs?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Saturday 27 November 2004 02:59 am, Konstantin Danilov wrote: > Hello List! > Do you know how many times can JOIN be used in a query? How many JOINs does > PostgreSQL support in ONE query? > I think you will run out of memory before hitting the lim

[GENERAL] ODBC driver troubles

Hi all, I've had a couple issues with the ODBC driver that ships with Postgres. I am using Access 2000 to link tables Postgres. Of the two issues, one is small: after I enabled row version in the ODBC driver - to fix a problem updating certain tables - the column xmin began appearing in al

Re: [GENERAL] Can one alter the format of a numeric column?

On Sat, Nov 27, 2004 at 10:50:47AM -0700, Michael Fuhr wrote: > On Sat, Nov 27, 2004 at 05:29:42PM +, Chris Green wrote: > > > Can one alter the precision of a numeric column? > > > > I want to reduce the precision of a column from 20,4 to 20,2. > > In PostgreSQL 8.0 (currently in beta) you

Re: [GENERAL] row-level deadlock problem

On Sat, Nov 27, 2004 at 12:58:36PM -0500, Tom Lane wrote: > Martijn van Oosterhout <[EMAIL PROTECTED]> writes: > > Well, there has to be something, since an UPDATE in Read Committed mode > > simply doesn't have any locks that can deadlock. > > You're mistaken; it takes a row lock on each row it up

Re: [GENERAL] row-level deadlock problem

On Sat, 27 Nov 2004, Alvaro Herrera wrote: > > You're mistaken; it takes a row lock on each row it updates. I'm not > > sure why the two UPDATEs are visiting the same rows in different orders, > > but if they do the failure is certainly possible. > > One of them could be using an indexscan while

Re: [GENERAL] comp.databases.postgresql.* groups and RFD

Marc G. Fournier From: <[EMAIL PROTECTED]> wrote in news:[EMAIL PROTECTED]: > Robert McClenon <[EMAIL PROTECTED]> writes: > >>Another thread on the topic of this RFD was cross-posted to >>pgsql.general. I didn't notice that it was cross-posted, and so >>cross-posted a reply to news.groups and p

Re: [GENERAL] Why the current setup of pgsql.* and comp.databases.postresql.general are BROKEN

Robert McClenon <[EMAIL PROTECTED]> wrote in news:[EMAIL PROTECTED]: > On 27 Nov 2004 18:32:35 GMT, Woodchuck Bill <[EMAIL PROTECTED]> > wrote: > >>Robert McClenon <[EMAIL PROTECTED]> wrote in >>news:[EMAIL PROTECTED]: >> >>> However, I will vote NO on the new group, because >>> it will in my o

Re: [GENERAL] Can you help solve restore problem?

* Tom Lane <[EMAIL PROTECTED]> [231104, 18:31]: > Ennio-Sr <[EMAIL PROTECTED]> writes: > > May I ask how to 'edit the COPY commands'? Perhaps you mean to edit the > > dump file and add "WITH NULL AS '-' " ? > > Yeah. > > > Mmmh, I tried to add that in the dump file (at the end of the COPY > > com

Re: [GENERAL] Query on exception handling in PL/pgSQL

I would second this motion as it relates to bulk inserts, I also love PG, but it's bulk insert/copy from file routines are definitely one area where it cannot claim to be "most advanced". My own wish list includes : *Copy to a new table - so that a table in the db is created - default is for al

Re: [GENERAL] row-level deadlock problem

Kamil Kaczkowski <[EMAIL PROTECTED]> writes: >>> You're mistaken; it takes a row lock on each row it updates. I'm not >>> sure why the two UPDATEs are visiting the same rows in different orders, >>> but if they do the failure is certainly possible. >> >> One of them could be using an indexscan wh

Re: [GENERAL] row-level deadlock problem

On Sat, 27 Nov 2004, Tom Lane wrote: > > In my case deadlock happens between two identical statements executed > > from different transactions and they have the same execution plan(index > > scan on one attribute - 'color' in schema I presented). > > That's a bit hard to believe; once the rows are

[GENERAL] PQoidValue have no use?

1. The oid may overlap; Insert may fail if i create a primary key on the oid column(Thank Richard Huxton and Michael Glaesemann). So I can't locate the record I Insert by the oid obtained by PQoidValue. Then, PQoidValue have no use? 2. I can use currtid2 to get the ctid after Delete. But in this m

Re: [GENERAL] (b)trim anomalies

Thomas F.O'Connell wrote: > I was just wondering why the btrim syntax that takes only a single > argument is not documented in 9.4 in the documentation (I checked both > 7.4 and 8.0 docs). > > This is in a 7.4.5 installation: > > pg=# \df btrim >List of functions > Res

Re: [GENERAL] GENERAL] Boolean error

The sql can be predigest to: select random() from a where random()<0.5; \d a Table "public.a" Column | Type | Modifiers +-+--- a | integer | This query can get about a half rows in the table 'a'. When search a row, postgresql calcuate random() twice.

[GENERAL] XUL and mozilla/firefox SQL extension

Considering using the firefox SQL extension combined with XUL to create a web application to access a database in PostGreSQL, wondering if anyone has attempted this, and how it turned out? Matthew Story ---(end of broadcast)--- TIP 9: the planner

[GENERAL] Large Object support for a DB FS

Hello, I am in a undergraduate course where we are doing some free-range research. I have selected to do a database file system for Linux built on PostgreSQL, and using FUSE to expose a POSIX interface. Originally I wanted to use large objects, but was turned away after discovering that there i

[GENERAL] SERIAL error

I'm using v. 7.4.2, and am encountering a problem with serial types. On an insert into a table that results in failure for any number of reasone (mostly violation of contraints) I've found that the Sequence for the column of type serial will still bump the value up once. This can be seen when en

Re: [GENERAL] SERIAL error

[EMAIL PROTECTED] wrote: I'm using v. 7.4.2, and am encountering a problem with serial types. On an insert into a table that results in failure for any number of reasone (mostly violation of contraints) I've found that the Sequence for the column of type serial will still bump the value up once. T

Re: [GENERAL] SERIAL error

On Sat, Nov 27, 2004 at 10:45:54PM -0600, [EMAIL PROTECTED] wrote: > > I'm using v. 7.4.2, and am encountering a problem with serial types. On an > insert into a table that results in failure for any number of reasone (mostly > violation of contraints) I've found that the Sequence for the column

Re: [GENERAL] SERIAL error

On Sat, 27 Nov 2004 [EMAIL PROTECTED] wrote: > > I'm using v. 7.4.2, and am encountering a problem with serial types. On an > insert into a table that results in failure for any number of reasone (mostly > violation of contraints) I've found that the Sequence for the column of type > serial will

Re: [GENERAL] AT TIME ZONE: "convert"?

Added TODO description: * Allow TIMESTAMP WITH TIME ZONE to store the original timezone information, either zone name or offset from UTC If the TIMESTAMP value is stored with a time zone name, interval computations should adjust based on the time zone rules, e.g. adding 24 hours to a tim

Re: [GENERAL] SERIAL error

Hi, On Sat, Nov 27, 2004 at 10:45:54PM -0600, [EMAIL PROTECTED] wrote: > > I'm using v. 7.4.2, and am encountering a problem with serial types. On an > insert into a table that results in failure for any number of reasone (mostly > violation of contraints) I've found that the Sequence for the co

Re: [GENERAL] SERIAL error

My mistake, in the future i will consult the FAQ first, though i'm a bit surprised i missed it in the documentation. Much thanks for the help. By generic uses i guess i meant the uses one might have for creating a sequence as an independant object in the database, and not the sequence in a table,

Re: [GENERAL] (b)trim anomalies

Not entirely. Per , btrim is documented as taking two arguments. There is a single-argument version that exists that trims whitespace if only the first argument is given (i.e., the characters to trim are omitted). This latter versi