Re: [GENERAL] Transactional-DDL DROP/CREATE TABLE

On 10 October 2016 at 14:49, Merlin Moncure wrote: > MVCC rules (which DDL generally fall under) try to interleave work as > much as possible which is the problem you're facing. Mmff. Yes, that exposes a fundamental misunderstanding on my part: I had thought that under MVCC things were done indep

Re: [GENERAL] Transactional-DDL DROP/CREATE TABLE

On Thu, Oct 6, 2016 at 4:21 AM, Geoff Winkless wrote: > Hi > > I have code that does (inside a single transaction) > > DROP TABLE IF EXISTS mytable; CREATE TABLE mytable > > Occasionally this produces > > ERROR: duplicate key value violates unique constraint > "pg_type_typname_nsp_index" DETA

Re: [GENERAL] Transactional-DDL DROP/CREATE TABLE

On 6 October 2016 at 18:33, Tom Lane wrote: > I'm a bit confused about exactly what the context is here. AFAICS, > the fragment you quoted should work as you expect, as long as the > table always exists beforehand. Then, the DROPs serialize the > transactions' access to the table and all is well.

Re: [GENERAL] Transactional-DDL DROP/CREATE TABLE

On 6 October 2016 at 18:25, Adrian Klaver wrote: > I do not see sarcasm, I see someone trying to work through what is a complex > scenario. When someone talks about things "magically working as you think it should" I see sarcasm. Perhaps I misread, in which case I apologise. >> _As far as the tr

Re: [GENERAL] Transactional-DDL DROP/CREATE TABLE

Geoff Winkless writes: > On 6 October 2016 at 16:47, Kevin Grittner wrote: >> I recommend using a transactional advisory lock to serialize these. > Thanks Kevin, that does seem like the best (although not particularly > pleasant) solution. I'm a bit confused about exactly what the context is he

Re: [GENERAL] Transactional-DDL DROP/CREATE TABLE

On 10/06/2016 09:09 AM, Geoff Winkless wrote: On 6 October 2016 at 16:57, Francisco Olarte wrote: You are contradicting yourself. First you say after the command it must not exist. Then you say to do it at commit time. If it is done at commit time you cannot guarantee it does not exist after th

Re: [GENERAL] Transactional-DDL DROP/CREATE TABLE

On 6 October 2016 at 16:47, Kevin Grittner wrote: > I recommend using a transactional advisory lock to serialize these. Thanks Kevin, that does seem like the best (although not particularly pleasant) solution. Geoff -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

Re: [GENERAL] Transactional-DDL DROP/CREATE TABLE

On 6 October 2016 at 16:57, Francisco Olarte wrote: > You are contradicting yourself. First you say after the command it > must not exist. Then you say to do it at commit time. If it is done at > commit time you cannot guarantee it does not exist after the command. I'm not contradicting myself at

Re: [GENERAL] Transactional-DDL DROP/CREATE TABLE

Geoff: On Thu, Oct 6, 2016 at 5:43 PM, Geoff Winkless wrote: > Nope. Serializable ignores the DROP, and then freezes on CREATE (and > then fails when the first transaction COMMITs). Yep, I tested it too. > Which is also broken, > because the transaction should fail if (at COMMIT time) the table

Re: [GENERAL] Transactional-DDL DROP/CREATE TABLE

On Thu, Oct 6, 2016 at 9:31 AM, Adrian Klaver wrote: > This is how I can trigger the ERROR: > > Session 1: > > test=# begin ; > BEGIN > test=# drop table if exists ddl_test; > NOTICE: table "ddl_test" does not exist, skipping > DROP TABLE > test=# create table ddl_test(id int); > CREATE TABLE >

Re: [GENERAL] Transactional-DDL DROP/CREATE TABLE

Adrian: On Thu, Oct 6, 2016 at 4:31 PM, Adrian Klaver wrote: > This is how I can trigger the ERROR: This is how you can trigger the ISSUE, IMO it is a correct behaviour. Anyway, your example lacks some important details: 1.- SHOW your isolation level. 2.- SHOW your command interleaving. Here i

Re: [GENERAL] Transactional-DDL DROP/CREATE TABLE

On 6 October 2016 at 15:04, Francisco Olarte wrote: > And anyway, what isolation level are you working on? Because it seems > you are using a weaker one than serializable, as I think serializable > should give you more or less what you are expecting ( not on commit > time, but second drop could pr

Re: [GENERAL] Transactional-DDL DROP/CREATE TABLE

On 10/06/2016 02:21 AM, Geoff Winkless wrote: Hi I have code that does (inside a single transaction) DROP TABLE IF EXISTS mytable; CREATE TABLE mytable Occasionally this produces ERROR: duplicate key value violates unique constraint "pg_type_typname_nsp_index" DETAIL: Key (typname, typna

Re: [GENERAL] Transactional-DDL DROP/CREATE TABLE

Hi Geoff: On Thu, Oct 6, 2016 at 1:58 PM, Geoff Winkless wrote: > But surely Transactional DDL implies that (it should appear that) nothing > happens until transaction-commit. That means "drop table if exists" should > drop the table if it exists at commit time, not drop the table if it didn't >

Re: [GENERAL] Transactional-DDL DROP/CREATE TABLE

On 6 Oct 2016 12:06 p.m., "Francisco Olarte" wrote: > > On Thu, Oct 6, 2016 at 11:21 AM, Geoff Winkless wrote: > > DROP TABLE IF EXISTS mytable; CREATE TABLE mytable > > > > Occasionally this produces > > > > ERROR: duplicate key value violates unique constraint > > "pg_type_typname_nsp_inde

Re: [GENERAL] Transactional-DDL DROP/CREATE TABLE

On Thu, Oct 6, 2016 at 11:21 AM, Geoff Winkless wrote: > DROP TABLE IF EXISTS mytable; CREATE TABLE mytable > > Occasionally this produces > > ERROR: duplicate key value violates unique constraint > "pg_type_typname_nsp_index" DETAIL: Key (typname, > typnamespace)=(mytable, 2200) already exis

Re: [GENERAL] Transactional DDL

Ron Mayer schrieb: Scott Marlowe wrote: On 8/14/07, Harpreet Dhaliwal <[EMAIL PROTECTED]> wrote: Hi, I read a few lines about SP compilation in postgres http://searchoracle.techtarget.com/originalContent/0,289142,sid41_gci1179016,00.html 1. stored procedure compilation is transactional. "You

Re: [GENERAL] Transactional DDL

So is there really any version control mechanism of functions in postgresql or not ? ~Harpreet On 8/18/07, Ron Mayer <[EMAIL PROTECTED]> wrote: > > Scott Marlowe wrote: > > On 8/14/07, Harpreet Dhaliwal <[EMAIL PROTECTED]> wrote: > >> Hi, > >> I read a few lines about SP compilation in postgres >

Re: [GENERAL] Transactional DDL

Scott Marlowe wrote: > On 8/14/07, Harpreet Dhaliwal <[EMAIL PROTECTED]> wrote: >> Hi, >> I read a few lines about SP compilation in postgres >> >> http://searchoracle.techtarget.com/originalContent/0,289142,sid41_gci1179016,00.html >> >> 1. stored procedure compilation is transactional. >> "You ca

Re: [GENERAL] Transactional DDL

al" <[EMAIL PROTECTED]>; "Scott Marlowe" <[EMAIL PROTECTED]> Sent: Wednesday, August 15, 2007 5:15 AM Subject: Re: [GENERAL] Transactional DDL On Wednesday 15 August 2007 1:58:07 am Harpreet Dhaliwal wrote: So you mean to say something like this as far as oracle is co

Re: [GENERAL] Transactional DDL

On 8/15/07, Harpreet Dhaliwal <[EMAIL PROTECTED]> wrote: > And this feature i.e. transactional DDL is not there in other major RDBMS > like sql server, oracle etc? The subject of transactional DDL and its prevalence was discussed in a May thread, "why postgresql over other RDBMS" (http://archives.

Re: [GENERAL] Transactional DDL

On Wednesday 15 August 2007 1:58:07 am Harpreet Dhaliwal wrote: > So you mean to say something like this as far as oracle is concerned: > > BEGIN > DDL 1 (commits right after its execution) > DDL 2 (commits right after its execution) > END > > That means there's no concept of putting DD

Re: [GENERAL] Transactional DDL

Harpreet Dhaliwal wrote: And this feature i.e. transactional DDL is not there in other major RDBMS like sql server, oracle etc? thanks ~Harpreet <...snipped earlier postings...> It surprised me when I saw Oracle's behavior. Informix supports DDL within transactions quite happily: create

Re: [GENERAL] Transactional DDL

Harpreet Dhaliwal wrote: And this feature i.e. transactional DDL is not there in other major RDBMS like sql server, oracle etc? You've had about 50 answers to that question already I think. The answer is No. -- Postgresql & php tutorials http://www.designmagick.com/ -

Re: [GENERAL] Transactional DDL

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/15/07 00:05, Harpreet Dhaliwal wrote: > And this feature i.e. transactional DDL is not there in other major > RDBMS like sql server, oracle etc? Define "major". Does it mean "popular" or "used on very large systems"? - -- Ron Johnson, Jr. Jeff

Re: [GENERAL] Transactional DDL

Harpreet Dhaliwal wrote: > So you mean to say something like this as far as oracle is concerned: > > BEGIN > DDL 1 (commits right after its execution) > DDL 2 (commits right after its execution) > END > > That means there's no concept of putting DDL statements in a transaction > in or

Re: [GENERAL] Transactional DDL

So you mean to say something like this as far as oracle is concerned: BEGIN DDL 1 (commits right after its execution) DDL 2 (commits right after its execution) END That means there's no concept of putting DDL statements in a transaction in oracle basically, right? Thanks, ~Harpreet

Re: [GENERAL] Transactional DDL

On 8/14/07, Harpreet Dhaliwal <[EMAIL PROTECTED]> wrote: > So you mean to say DDL statements can't be put in one single transaction in > Oracle ? You can put them in, but then they will cause the previous DMK to be silently committed ---(end of broadcast)--

Re: [GENERAL] Transactional DDL

And this feature i.e. transactional DDL is not there in other major RDBMS like sql server, oracle etc? thanks ~Harpreet On 8/15/07, Tom Lane <[EMAIL PROTECTED]> wrote: > > "Harpreet Dhaliwal" <[EMAIL PROTECTED]> writes: > > I read a few lines about SP compilation in postgres > > > http://searcho

Re: [GENERAL] Transactional DDL

"Harpreet Dhaliwal" <[EMAIL PROTECTED]> writes: > I read a few lines about SP compilation in postgres > http://searchoracle.techtarget.com/originalContent/0,289142,sid41_gci1179016,00.html > Is this what the Transactional DDL feature of postgresql talks about ? I'd say it's one very small aspect

Re: [GENERAL] Transactional DDL

So you mean to say DDL statements can't be put in one single transaction in Oracle ? On 8/15/07, Scott Marlowe <[EMAIL PROTECTED]> wrote: > > On 8/14/07, Harpreet Dhaliwal <[EMAIL PROTECTED]> wrote: > > > > Hi, > > I read a few lines about SP compilation in postgres > > > > > http://searchoracle.t

Re: [GENERAL] Transactional DDL

On 8/14/07, Harpreet Dhaliwal <[EMAIL PROTECTED]> wrote: > > Hi, > I read a few lines about SP compilation in postgres > > http://searchoracle.techtarget.com/originalContent/0,289142,sid41_gci1179016,00.html > > 1. stored procedure compilation is transactional. > "You can recompile a stored procedu

Re: [GENERAL] Transactional DDL

On Sat, Jun 02, 2007 at 04:51:13PM -0400, Harpreet Dhaliwal wrote: > my bad.. i replied to that in a wrong thread. sorry That is one of many reasons that smart people don't top post. Had you decided not to top post, you would have realized instantly that you were in the wrong thread. If there is

Re: [GENERAL] Transactional DDL

On Sun, 03 Jun 2007 01:39:11 +0200, Tom Lane <[EMAIL PROTECTED]> wrote: "Jaime Casanova" <[EMAIL PROTECTED]> writes: Tom's example seems to show that mysql inserts a commit immidiatelly after a DDL but this one example shows the thing is worse than that. Actually, I think their behavior is ju

Re: [GENERAL] Transactional DDL

"Jaime Casanova" <[EMAIL PROTECTED]> writes: > Tom's example seems to show that mysql inserts a commit immidiatelly > after a DDL but this one example shows the thing is worse than that. Actually, I think their behavior is just "DDL issues a COMMIT", so that after that you are out of the transacti

Re: [GENERAL] Transactional DDL

> On 6/2/07, *Jasbinder Singh Bali* <[EMAIL PROTECTED] > > wrote: > > On 6/2/07, *Michael Glaesemann* < [EMAIL PROTECTED] > > wrote: > > On Jun 2, 2007, at 11:08 , Harpreet Dhaliwal wrote: > > > Whats so novel about postgr

Re: [GENERAL] Transactional DDL

On 6/2/07, PFC <[EMAIL PROTECTED]> wrote: >> This is what happens in every RDBMS. Whats so special about postgres >> then? mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE ble ( id INTEGER ) ENGINE=InnoDB; Query OK, 0 rows affected (0.09 sec) mysql> INSERT INTO blehh

Re: [GENERAL] Transactional DDL

my bad.. i replied to that in a wrong thread. sorry On 6/2/07, Leif B. Kristensen <[EMAIL PROTECTED]> wrote: On Saturday 2. June 2007 20:39, Ron Johnson wrote: >You were politely asked not to top-post. > >On 06/02/07 11:46, Harpreet Dhaliwal wrote: >> So, while writing any technical document, w

Re: [GENERAL] Transactional DDL

On Saturday 2. June 2007 20:39, Ron Johnson wrote: >You were politely asked not to top-post. > >On 06/02/07 11:46, Harpreet Dhaliwal wrote: >> So, while writing any technical document, would it be wrong to >> mention stored procedures in postgresql? >> what is the general convention? > >Did I miss

Re: [GENERAL] Transactional DDL

Russ Brown <[EMAIL PROTECTED]> writes: > Harpreet Dhaliwal wrote: >> Whats so different in postgresql then? > Try doing the same test in MySQL (using InnoDB so you get a supposedly > ACID compliant table type). > Or even in Oracle. Examples (using mysql 5.0.40, reasonably current): mysql> creat

Re: [GENERAL] Transactional DDL

This is what happens in every RDBMS. Whats so special about postgres then? mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE ble ( id INTEGER ) ENGINE=InnoDB; Query OK, 0 rows affected (0.09 sec) mysql> INSERT INTO ble VALUES (1),(2),(3); Query OK, 3 rows affect

Re: [GENERAL] Transactional DDL

On 06/02/07 13:35, Jasbinder Singh Bali wrote: On 6/2/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote: On Jun 2, 2007, at 11:08 , Harpreet Dhaliwal wrote: > Whats so novel about postgresql here? > This would happen in any RDBMS. right? > You induced divide by zero exception that crashed the

Re: [GENERAL] Transactional DDL

Harpreet Dhaliwal wrote: > > > On 6/2/07, *Jasbinder Singh Bali* <[EMAIL PROTECTED] > > wrote: > > > > On 6/2/07, *Michael Glaesemann* < [EMAIL PROTECTED] > > wrote: > > > On Jun 2, 2007, at 11:08 , Harpreet Dhaliwal wrote:

Re: [GENERAL] Transactional DDL

You were politely asked not to top-post. On 06/02/07 11:46, Harpreet Dhaliwal wrote: So, while writing any technical document, would it be wrong to mention stored procedures in postgresql? what is the general convention? Did I miss something? What does "stored procedures" have to do with "

Re: [GENERAL] Transactional DDL

On 6/2/07, Jasbinder Singh Bali <[EMAIL PROTECTED]> wrote: On 6/2/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote: > > > On Jun 2, 2007, at 11:08 , Harpreet Dhaliwal wrote: > > > Whats so novel about postgresql here? > > This would happen in any RDBMS. right? > > You induced divide by zero ex

Re: [GENERAL] Transactional DDL

On 6/2/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote: On Jun 2, 2007, at 11:08 , Harpreet Dhaliwal wrote: > Whats so novel about postgresql here? > This would happen in any RDBMS. right? > You induced divide by zero exception that crashed the whole > transaction and it did not create the ta

Re: [GENERAL] Transactional DDL

On Jun 2, 2007, at 11:08 , Harpreet Dhaliwal wrote: Whats so novel about postgresql here? This would happen in any RDBMS. right? You induced divide by zero exception that crashed the whole transaction and it did not create the table bar? [Please don't top-post. It makes the discussion hard

Re: [GENERAL] Transactional DDL

So, while writing any technical document, would it be wrong to mention stored procedures in postgresql? what is the general convention? On 6/2/07, Dawid Kuroczko <[EMAIL PROTECTED]> wrote: On 6/2/07, Jasbinder Singh Bali <[EMAIL PROTECTED]> wrote: > But its said that transactions in any RDBMS f

Re: [GENERAL] Transactional DDL

On 6/2/07, Jasbinder Singh Bali <[EMAIL PROTECTED]> wrote: But its said that transactions in any RDBMS follow ACID properties. So if i put a create table and an Insert statement in the same begin end block as one single transactioin, won't both create and insert follow acid property, being in one

Re: [GENERAL] Transactional DDL

Whats so novel about postgresql here? This would happen in any RDBMS. right? You induced divide by zero exception that crashed the whole transaction and it did not create the table bar? I can't see any Transactional DDL philosophy here. Could you please throw some more light on it to point out the

Re: [GENERAL] Transactional DDL

On Jun 2, 2007, at 10:12 , Jasbinder Singh Bali wrote: But its said that transactions in any RDBMS follow ACID properties. So if i put a create table and an Insert statement in the same begin end block as one single transactioin, won't both create and insert follow acid property, being in o

Re: [GENERAL] Transactional DDL

But its said that transactions in any RDBMS follow ACID properties. So if i put a create table and an Insert statement in the same begin end block as one single transactioin, won't both create and insert follow acid property, being in one single trasaction, and either both get committed or none, t

Re: [GENERAL] Transactional DDL

On 6/2/07, Jasbinder Singh Bali <[EMAIL PROTECTED]> wrote: [snip] I believe that if a database supports transactional ddl then ddl1 and ddl2 would commit together as a batch And If a Db doesn't support this transactional DDL feature then ddl1 executes and commits without even caring about ddl2. R