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

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 >

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

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

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

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

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

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

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

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

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

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,

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,

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

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

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, >

[GENERAL] Transactional-DDL DROP/CREATE TABLE

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, typnamespace)=(mytable, 2200) already exists. I can

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

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

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 can recompile a

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

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 oracle

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. Jefferson

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

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

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 DDL

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

Re: [GENERAL] Transactional DDL

Dhaliwal [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 concerned: BEGIN DDL 1

[GENERAL] Transactional DDL

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 procedure on a live system, and only transactions starting after that

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 procedure on a

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

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 of

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

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

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 just

[GENERAL] Transactional DDL

Hi, A few days back, it was commented by someone in the community that Postgres has this Transactional DDL feature. What I understand about Transactional DDL is something like this: begin --ddl 1 --ddl 2 end; I believe that if a database supports transactional ddl then ddl1 and

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.

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,

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

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

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

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

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

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 table

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 exception that

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

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

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

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 affected

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 create table

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 something?

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, would

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 ble

Re: [GENERAL] Transactional DDL

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

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 transaction and