Thread: Re: Feature discussion: Should syntax errors abort a transaction?

And I will be pleased that data is gone! I really did not expect anything but this.
If I need such tolerant behavior, then this shall be a feature of my special app, not a feature of the database... If
thedeveloper does not know how to write sql, then is time to learn. If the problem is the dynamic generated Sql, then I
mustwrite more test cases to cover these new scenarios. But IMHO, database must fail always (syntax or not...). 

Regards,

Edson

Scott Marlowe <scott.marlowe@gmail.com> escreveu:

>On Tue, Jun 19, 2012 at 8:50 AM, Edson Richter <edsonrichter@hotmail.com> wrote:
>> There is also the case of dynamically generated sql statements based on user selection... being syntax or not, I
wouldnever want half job done. Thia is the purpose of transactions: or all or nothing... 
>
>This this this, and again, this.  Imagine:
>
>begin;
>insert into tableb selcet * from tableb;
>truncate tableb;
>commit;
>
>What should happen when we get to the error on the second line?  Keep
>going?  Boom, data gone because of a syntax error.
>

But that data was supposed to get transferred into another table
first!  Data shouldn't just disappear like that. If you want that kind
of behaviour use a different db that likes to throw your data away
when it shouldn't.

On Tue, Jun 19, 2012 at 1:09 PM, Edson Richter <edsonrichter@hotmail.com> wrote:
> And I will be pleased that data is gone! I really did not expect anything but this.
> If I need such tolerant behavior, then this shall be a feature of my special app, not a feature of the database... If
thedeveloper does not know how to write sql, then is time to learn. If the problem is the dynamic generated Sql, then I
mustwrite more test cases to cover these new scenarios. But IMHO, database must fail always (syntax or not...). 
>
> Regards,
>
> Edson
>
> Scott Marlowe <scott.marlowe@gmail.com> escreveu:
>
>>On Tue, Jun 19, 2012 at 8:50 AM, Edson Richter <edsonrichter@hotmail.com> wrote:
>>> There is also the case of dynamically generated sql statements based on user selection... being syntax or not, I
wouldnever want half job done. Thia is the purpose of transactions: or all or nothing... 
>>
>>This this this, and again, this.  Imagine:
>>
>>begin;
>>insert into tableb selcet * from tableb;
>>truncate tableb;
>>commit;
>>
>>What should happen when we get to the error on the second line?  Keep
>>going?  Boom, data gone because of a syntax error.
>>



--
To understand recursion, one must first understand recursion.

But that data was supposed to get transferred into another table
first!  Data shouldn't just disappear like that. If you want that kind
of behaviour use a different db that likes to throw your data away
when it shouldn't.

On Tue, Jun 19, 2012 at 1:09 PM, Edson Richter <edsonrichter@hotmail.com> wrote:

And I will be pleased that data is gone! I really did not expect anything but this.
If I need such tolerant behavior, then this shall be a feature of my special app, not a feature of the database... If the developer does not know how to write sql, then is time to learn. If the problem is the dynamic generated Sql, then I must write more test cases to cover these new scenarios. But IMHO, database must fail always (syntax or not...).

Regards,

Edson

Scott Marlowe <scott.marlowe@gmail.com> escreveu:

On Tue, Jun 19, 2012 at 8:50 AM, Edson Richter <edsonrichter@hotmail.com> wrote:

There is also the case of dynamically generated sql statements based on user selection... being syntax or not, I would never want half job done. Thia is the purpose of transactions: or all or nothing...

This this this, and again, this.  Imagine:

begin;
insert into tableb selcet * from tableb;
truncate tableb;
commit;

What should happen when we get to the error on the second line?  Keep
going?  Boom, data gone because of a syntax error.

On Tue, Jun 19, 2012 at 6:19 PM, Edson Richter <edsonrichter@hotmail.com> wrote:
> According to documentation,
>
> "TRUNCATE is transaction-safe with respect to the data in the tables: the
> truncation will be safely rolled back if the surrounding transaction does
> not commit."
>
> You will find this description at following page:
>
> http://www.postgresql.org/docs/9.0/static/sql-truncate.html
>
> So, when you have the "syntax error" on second line, then transaction is
> rolled back (cannot proceed: and that's why Syntax Errors should be treated
> as any other error) and your data is safe.

Yes but the discussion was that the syntax error SHOULDN'T cause a
roll back, and I was giving an example of when a transaction should
have rolled back but wouldn't have if syntax errors didn't cause
rollback.

In a different vein, the issue of "interactive" versus "scripted" is
something I don't want to take chances on getting wrong.  If I'm in
the psql terminal and type \i /tmp/somesqlile.sql is that interactive
or scripted?  How can psql know?  Should it know?  Can I trust it to
make the right decision of interactive versus scripted each time?

I generally put more than two lines of sql in a text file, edit it,
and throw at begin; on it.  run it with \i and then commit or rollback
as needed.  It documents what you did so you can check it in
somewhere, and makes it repeatable.

Em 19/06/2012 22:26, Scott Marlowe escreveu:
> On Tue, Jun 19, 2012 at 6:19 PM, Edson Richter <edsonrichter@hotmail.com> wrote:
>> According to documentation,
>>
>> "TRUNCATE is transaction-safe with respect to the data in the tables: the
>> truncation will be safely rolled back if the surrounding transaction does
>> not commit."
>>
>> You will find this description at following page:
>>
>> http://www.postgresql.org/docs/9.0/static/sql-truncate.html
>>
>> So, when you have the "syntax error" on second line, then transaction is
>> rolled back (cannot proceed: and that's why Syntax Errors should be treated
>> as any other error) and your data is safe.
> Yes but the discussion was that the syntax error SHOULDN'T cause a
> roll back, and I was giving an example of when a transaction should
> have rolled back but wouldn't have if syntax errors didn't cause
> rollback.
>
> In a different vein, the issue of "interactive" versus "scripted" is
> something I don't want to take chances on getting wrong.  If I'm in
> the psql terminal and type \i /tmp/somesqlile.sql is that interactive
> or scripted?  How can psql know?  Should it know?  Can I trust it to
> make the right decision of interactive versus scripted each time?
>
> I generally put more than two lines of sql in a text file, edit it,
> and throw at begin; on it.  run it with \i and then commit or rollback
> as needed.  It documents what you did so you can check it in
> somewhere, and makes it repeatable.
AFAIK, psql open one connection to database - and the transaction is
connection related (two different connections does not share a
transaction). I really mean AFAIK. At this point, someone else with more
internals knowledge can give some light here.

My argument was pro "syntax error should rollback" to make things
safe... :-). Assuming psql is working with only one connection, even in
interactive mode, the transaction should remains valid.

Regards,