Professional Web Applications Themes

Select after insert to the unique column - PostgreSQL / PGSQL

On Wed, Dec 08, 2004 at 14:50:04 +0100, Julian Legeny <legenysofthome.net> wrote: > Hello, > > Then I want to process command > select count(*) from UNIQUE_COLUMN_TEST > that I want to know how many records was already inserted before id > faied. > > But when I try to process that SELECT COUNT(*), there is error > occured again: > > org.postgresql.util.PSQLException: > ERROR: current transaction is aborted, commands ignored until end of transaction block > > How can I solve this? Depending on what you really want to do, you could do each insert in its own transaction. ...

  1. #1

    Default Re: Select after insert to the unique column

    On Wed, Dec 08, 2004 at 14:50:04 +0100,
    Julian Legeny <legenysofthome.net> wrote:
    > Hello,
    >
    > Then I want to process command
    > select count(*) from UNIQUE_COLUMN_TEST
    > that I want to know how many records was already inserted before id
    > faied.
    >
    > But when I try to process that SELECT COUNT(*), there is error
    > occured again:
    >
    > org.postgresql.util.PSQLException:
    > ERROR: current transaction is aborted, commands ignored until end of transaction block
    >
    > How can I solve this?
    Depending on what you really want to do, you could do each insert in its
    own transaction.

    If you don't want any of the inserts to succeed if there are problems, then
    you should do the counting in the application doing the inserts.

    ---------------------------(end of broadcast)---------------------------
    TIP 4: Don't 'kill -9' the postmaster

    Bruno Wolff III Guest

  2. #2

    Default Re: Select after insert to the unique column

    If you attempted the inserts within a single transaction and any of
    them fail, they will all fail. The server will automatically undo any
    and all changes made by the transaction, and any further steps in the
    transaction will simply result in the error message you are getting.
    You will not be able to (successfully) issue any further database
    commands until you end the transaction and start a new one.

    On Dec 11, 2004, at 2:29 PM, Bruno Wolff III wrote:
    > On Wed, Dec 08, 2004 at 14:50:04 +0100,
    > Julian Legeny <legenysofthome.net> wrote:
    >> Hello,
    >>
    >> Then I want to process command
    >> select count(*) from UNIQUE_COLUMN_TEST
    >> that I want to know how many records was already inserted before id
    >> faied.
    >>
    >> But when I try to process that SELECT COUNT(*), there is error
    >> occured again:
    >>
    >> org.postgresql.util.PSQLException:
    >> ERROR: current transaction is aborted, commands ignored until end
    >> of transaction block
    >>
    >> How can I solve this?
    >
    > Depending on what you really want to do, you could do each insert in
    > its
    > own transaction.
    >
    > If you don't want any of the inserts to succeed if there are problems,
    > then
    > you should do the counting in the application doing the inserts.
    >
    > ---------------------------(end of
    > broadcast)---------------------------
    > TIP 4: Don't 'kill -9' the postmaster
    >
    >
    -----------------------------------------------------------
    Frank D. Engel, Jr. <fde101fjrhome.net>

    $ ln -s /usr/share/kjvbible /usr/manual
    $ true | cat /usr/manual | grep "John 3:16"
    John 3:16 For God so loved the world, that he gave his only begotten
    Son, that whosoever believeth in him should not perish, but have
    everlasting life.
    $



    __________________________________________________ _________
    $0 Web Hosting with up to 120MB web space, 1000 MB Transfer
    10 Personalized POP and Web E-mail Accounts, and much more.
    Signup at [url]www.doteasy.com[/url]


    ---------------------------(end of broadcast)---------------------------
    TIP 6: Have you searched our list archives?

    [url]http://archives.postgresql.org[/url]

    Frank D. Engel, Jr. Guest

  3. #3

    Default Re: Select after insert to the unique column

    Thank you for your answer. I think it's very interesting behaviour. Is
    it a feature or bug ?

    I have try this my jUnit test for another DB systems (e.g. Oracle 9i,
    MS SQL Server 2000, MySQL, DB2, Sybase, SAP DB) and it works for each of
    these databases (it was possible tu run next command successfully after
    an exception occured before).

    With baset regards,

    Julian Legeny


    Monday, December 13, 2004, 4:26:24 PM, you wrote:

    FDEJ> If you attempted the inserts within a single transaction and any of
    FDEJ> them fail, they will all fail. The server will automatically undo any
    FDEJ> and all changes made by the transaction, and any further steps in the
    FDEJ> transaction will simply result in the error message you are getting.
    FDEJ> You will not be able to (successfully) issue any further database
    FDEJ> commands until you end the transaction and start a new one.

    FDEJ> On Dec 11, 2004, at 2:29 PM, Bruno Wolff III wrote:
    >> On Wed, Dec 08, 2004 at 14:50:04 +0100,
    >> Julian Legeny <legenysofthome.net> wrote:
    >>> Hello,
    >>>
    >>> Then I want to process command
    >>> select count(*) from UNIQUE_COLUMN_TEST
    >>> that I want to know how many records was already inserted before id
    >>> faied.
    >>>
    >>> But when I try to process that SELECT COUNT(*), there is error
    >>> occured again:
    >>>
    >>> org.postgresql.util.PSQLException:
    >>> ERROR: current transaction is aborted, commands ignored until end
    >>> of transaction block
    >>>
    >>> How can I solve this?
    >>
    >> Depending on what you really want to do, you could do each insert in
    >> its
    >> own transaction.
    >>
    >> If you don't want any of the inserts to succeed if there are problems,
    >> then
    >> you should do the counting in the application doing the inserts.
    >>
    >> ---------------------------(end of
    >> broadcast)---------------------------
    >> TIP 4: Don't 'kill -9' the postmaster
    >>
    >>

    ---------------------------(end of broadcast)---------------------------
    TIP 9: the planner will ignore your desire to choose an index scan if your
    joining column's datatypes do not match

    juleni@livetrade.cz Guest

  4. #4

    Default Re: Select after insert to the unique column

    On Mon, Dec 13, 2004 at 17:04:17 +0100,
    [email]julenilivetrade.cz[/email] wrote:
    > Thank you for your answer. I think it's very interesting behaviour. Is
    > it a feature or bug ?
    Until version 8 (which is in release candidate status now), there was
    no way to recover from an error within a transaction other than aborting
    the transaction. With version 8 you will be able to use savepoints to
    allow for recovery from errors within a transaction.

    The best answer to your question above, is that it is a limitation and
    not really a feature and definitely not a bug.

    ---------------------------(end of broadcast)---------------------------
    TIP 3: if posting/reading through Usenet, please send an appropriate
    subscribe-nomail command to [email]majordomopostgresql.org[/email] so that your
    message can get through to the mailing list cleanly

    Bruno Wolff III Guest

Similar Threads

  1. Replies: 0
    Last Post: February 9th, 02:06 AM
  2. Replies: 0
    Last Post: November 11th, 02:31 PM
  3. Replies: 0
    Last Post: September 26th, 10:27 PM
  4. What's faster - loop for insert or insert...select.
    By mr. modus in forum Coldfusion Database Access
    Replies: 2
    Last Post: December 10th, 01:57 AM
  5. Insert into <table w/ text column> select distinct ...
    By Adam Nester in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: June 30th, 04:38 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139