Thread: bug report: slow getColumnTypeName

On 10/05/2012 07:07 AM, Eyal Wilde wrote:
>
>                      ResultSetMetaData __md = __rs.getMetaData();
>   //this is fine
>                      String __sf1name = __md.getColumnName(1);
>    //this is fine
>                      int __if1type = __md.getColumnType(1);
>     //this is fine
>                      String __sf1type =
> __md.getColumnTypeName(1); //this is SLOW!! ~15msec

Can you wrap that up into a test case (SQL schema and Java code) that's
self contained and runnable?

--
Craig Ringer

On Fri, 5 Oct 2012, Eyal Wilde wrote:

>
>                     ResultSetMetaData __md = __rs.getMetaData();    //this
> is fine
>                     String __sf1name = __md.getColumnName(1);       //this
> is fine
>                     int __if1type = __md.getColumnType(1);              
>  //this is fine
>                     String __sf1type = __md.getColumnTypeName(1); //this is
> SLOW!! ~15msec
>

getColumnTypeName requires going back to the server to fetch the
additional information about the source table that is not needed for
just getColumnType.  So naturally that takes longer.

Kris Jurka

I verified with wireshark that  getColumnTypeName indeed do a request to the postgres server. This happens with 9.1-901 and 9.1-903 BUT NOT WITH 9.0-801!!

So, the reason for the slowness seems to be obviouse now.

it will take me some time to wrap up it into a test case. I would realy like to help, but please let me know if it's realy necessary.

I verified with wireshark that  getColumnTypeName indeed do a request to the postgres server. This happens with 9.1-901 and 9.1-903 BUT NOT WITH 9.0-801!!

So, the reason for the slowness seems to be obviouse now.

it will take me some time to wrap up it into a test case. I would realy like to help, but please let me know if it's realy necessary.

Eyal Wilde wrote:
> I verified with wireshark that  getColumnTypeName indeed do a request to
> the postgres server. This happens with 9.1-901 and 9.1-903 BUT NOT WITH
> 9.0-801!!
>
> So, the reason for the slowness seems to be obviouse now.
>
> it will take me some time to wrap up it into a test case. I would realy
> like to help, but please let me know if it's realy necessary.
>
> On Oct 5, 2012 1:07 AM, "Eyal Wilde" <eyal@impactsoft.co.il
> <mailto:eyal@impactsoft.co.il>> wrote:
>
>
>                          ResultSetMetaData __md = __rs.getMetaData();
>       //this is fine
>                          String __sf1name = __md.getColumnName(1);
>        //this is fine
>                          int __if1type = __md.getColumnType(1);
>             //this is fine
>                          String __sf1type =
>     __md.getColumnTypeName(1); //this is SLOW!! ~15msec
>
>     postgres server version is 9.1.5
>     jdbc version, i checked both 9.1-901 and 9.1-903
>     and... BTW, 9.0-801 works good (while connected pg9.1)!

There does appear to be a change in the code that may have created the
slowness that you are observing. Please try a a test case in which two
back to back getColumnTypeName() calls are made. Is there a difference
in time between the first and second and is there still on the second
call a request to the postgres server.

The code between 9.0-801 and later version does have a change in it
that looks like for the caching for field metadata through fetchFieldMetaData().
That method is called in the later versions for getColumnTypeName()
with isAutoIncrement(), with was also added in later versions.

danap.

I also confirm the performance regression.

Testing on Fedora17 64bit + PostgreSQL 9.1 + jdk 1.6.0_33, localhost
server, I get these numbers:
driver 802
getColumnType time (ms): 2777
getColumnTypeName time (ms): 1847
both time (ms): 1948

driver 903
getColumnType time (ms): 3044
getColumnTypeName time (ms): 27123
both time (ms): 25142

driver 1000
getColumnType time (ms): 2928
getColumnTypeName time (ms): 27214
both time (ms): 26407

During the getColumnTypeName tests postgresql daemon used 100% cpu time.

Here is the full test class:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;


public class Main
{
    public static void main( String arg[] )
        throws Exception
    {
        Class.forName( "org.postgresql.Driver" );
        Connection conn = DriverManager.getConnection(
"jdbc:postgresql://localhost/test", "postgres", "" );
        Statement create = conn.createStatement();
        create.execute( "CREATE TABLE t(id SERIAL, name VARCHAR);" );
        create.close();
        long l = System.currentTimeMillis();
        for( int i = 0; i < 10000; ++i )
        {
            Statement stm = conn.createStatement();
            stm.execute( "SELECT id, name FROM t;" );
            ResultSet rs = stm.getResultSet();
            ResultSetMetaData rsmd = rs.getMetaData();
            rsmd.getColumnType( 1 );
//            rsmd.getColumnTypeName( 1 );
            rs.close();
            stm.close();
        }
        System.out.println( "getColumnType time (ms): " + (
System.currentTimeMillis() - l ) );
        l = System.currentTimeMillis();
        for( int i = 0; i < 10000; ++i )
        {
            Statement stm = conn.createStatement();
            stm.execute( "SELECT id, name FROM t;" );
            ResultSet rs = stm.getResultSet();
            ResultSetMetaData rsmd = rs.getMetaData();
//            rsmd.getColumnType( 1 );
            rsmd.getColumnTypeName( 1 );
            rs.close();
            stm.close();
        }
        System.out.println( "getColumnTypeName time (ms): " + (
System.currentTimeMillis() - l ) );
        l = System.currentTimeMillis();
        for( int i = 0; i < 10000; ++i )
        {
            Statement stm = conn.createStatement();
            stm.execute( "SELECT id, name FROM t;" );
            ResultSet rs = stm.getResultSet();
            ResultSetMetaData rsmd = rs.getMetaData();
            rsmd.getColumnType( 1 );
            rsmd.getColumnTypeName( 1 );
            rs.close();
            stm.close();
        }
        System.out.println( "both time (ms): " + ( System.currentTimeMillis() - l ) );
        Statement drop = conn.createStatement();
        drop.execute( "DROP TABLE t;" );
        drop.close();
        conn.close();
    }
}


Luis Flores

On Tue, Oct 9, 2012 at 5:48 PM, dmp <danap@ttc-cmc.net> wrote:
> Eyal Wilde wrote:
>>
>> I verified with wireshark that  getColumnTypeName indeed do a request to
>> the postgres server. This happens with 9.1-901 and 9.1-903 BUT NOT WITH
>> 9.0-801!!
>>
>> So, the reason for the slowness seems to be obviouse now.
>>
>> it will take me some time to wrap up it into a test case. I would realy
>> like to help, but please let me know if it's realy necessary.
>>
>> On Oct 5, 2012 1:07 AM, "Eyal Wilde" <eyal@impactsoft.co.il
>> <mailto:eyal@impactsoft.co.il>> wrote:
>>
>>
>>                          ResultSetMetaData __md = __rs.getMetaData();
>>       //this is fine
>>                          String __sf1name = __md.getColumnName(1);
>>        //this is fine
>>                          int __if1type = __md.getColumnType(1);
>>             //this is fine
>>                          String __sf1type =
>>     __md.getColumnTypeName(1); //this is SLOW!! ~15msec
>>
>>     postgres server version is 9.1.5
>>     jdbc version, i checked both 9.1-901 and 9.1-903
>>     and... BTW, 9.0-801 works good (while connected pg9.1)!
>
>
> There does appear to be a change in the code that may have created the
> slowness that you are observing. Please try a a test case in which two
> back to back getColumnTypeName() calls are made. Is there a difference
> in time between the first and second and is there still on the second
> call a request to the postgres server.
>
> The code between 9.0-801 and later version does have a change in it
> that looks like for the caching for field metadata through
> fetchFieldMetaData().
> That method is called in the later versions for getColumnTypeName()
> with isAutoIncrement(), with was also added in later versions.
>
> danap.
>
>
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc

I've looked at the AbstractJdbc2ResultSetMetadata getColumnTypeName,
for version 1000 and 802.

The change is simple, there is an extra call to isAutoIncrement, to be
able to return correct values in the serial and bigserial cases.

The isAutoIncrement call is slow because it triggers
fetchFieldMetadata, witch get all metada for all fields.

One simple optimization is to change the current method to:

    public String getColumnTypeName(int column) throws SQLException
    {
        String type = getPGType(column);
        if ( ( "int4".equals(type) || "int8".equals(type) ) &&
isAutoIncrement(column)) {
            if ("int4".equals(type)) {
                return "serial";
            } else if ("int8".equals(type)) {
                return "bigserial";
            }
        }

        return type;
    }

In this case, the isAutoIncrement is only called on int4 and int8
columns, causing the performance for all the other column types to
remain the same.

May they are better options, but I think this change is good, it
delays fetching metadata, and speeds up the method, without side
effects.

Luis Flores

On Wed, Oct 10, 2012 at 11:10 PM, Luis Flores
<luiscamposflores@gmail.com> wrote:
> I also confirm the performance regression.
>
> Testing on Fedora17 64bit + PostgreSQL 9.1 + jdk 1.6.0_33, localhost
> server, I get these numbers:
> driver 802
> getColumnType time (ms): 2777
> getColumnTypeName time (ms): 1847
> both time (ms): 1948
>
> driver 903
> getColumnType time (ms): 3044
> getColumnTypeName time (ms): 27123
> both time (ms): 25142
>
> driver 1000
> getColumnType time (ms): 2928
> getColumnTypeName time (ms): 27214
> both time (ms): 26407
>
> During the getColumnTypeName tests postgresql daemon used 100% cpu time.
>
> Here is the full test class:
>
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.ResultSet;
> import java.sql.ResultSetMetaData;
> import java.sql.Statement;
>
>
> public class Main
> {
>         public static void main( String arg[] )
>                 throws Exception
>         {
>                 Class.forName( "org.postgresql.Driver" );
>                 Connection conn = DriverManager.getConnection(
> "jdbc:postgresql://localhost/test", "postgres", "" );
>                 Statement create = conn.createStatement();
>                 create.execute( "CREATE TABLE t(id SERIAL, name VARCHAR);" );
>                 create.close();
>                 long l = System.currentTimeMillis();
>                 for( int i = 0; i < 10000; ++i )
>                 {
>                         Statement stm = conn.createStatement();
>                         stm.execute( "SELECT id, name FROM t;" );
>                         ResultSet rs = stm.getResultSet();
>                         ResultSetMetaData rsmd = rs.getMetaData();
>                         rsmd.getColumnType( 1 );
> //                      rsmd.getColumnTypeName( 1 );
>                         rs.close();
>                         stm.close();
>                 }
>                 System.out.println( "getColumnType time (ms): " + (
> System.currentTimeMillis() - l ) );
>                 l = System.currentTimeMillis();
>                 for( int i = 0; i < 10000; ++i )
>                 {
>                         Statement stm = conn.createStatement();
>                         stm.execute( "SELECT id, name FROM t;" );
>                         ResultSet rs = stm.getResultSet();
>                         ResultSetMetaData rsmd = rs.getMetaData();
> //                      rsmd.getColumnType( 1 );
>                         rsmd.getColumnTypeName( 1 );
>                         rs.close();
>                         stm.close();
>                 }
>                 System.out.println( "getColumnTypeName time (ms): " + (
> System.currentTimeMillis() - l ) );
>                 l = System.currentTimeMillis();
>                 for( int i = 0; i < 10000; ++i )
>                 {
>                         Statement stm = conn.createStatement();
>                         stm.execute( "SELECT id, name FROM t;" );
>                         ResultSet rs = stm.getResultSet();
>                         ResultSetMetaData rsmd = rs.getMetaData();
>                         rsmd.getColumnType( 1 );
>                         rsmd.getColumnTypeName( 1 );
>                         rs.close();
>                         stm.close();
>                 }
>                 System.out.println( "both time (ms): " + ( System.currentTimeMillis() - l ) );
>                 Statement drop = conn.createStatement();
>                 drop.execute( "DROP TABLE t;" );
>                 drop.close();
>                 conn.close();
>         }
> }
>
>
> Luis Flores
>
> On Tue, Oct 9, 2012 at 5:48 PM, dmp <danap@ttc-cmc.net> wrote:
>> Eyal Wilde wrote:
>>>
>>> I verified with wireshark that  getColumnTypeName indeed do a request to
>>> the postgres server. This happens with 9.1-901 and 9.1-903 BUT NOT WITH
>>> 9.0-801!!
>>>
>>> So, the reason for the slowness seems to be obviouse now.
>>>
>>> it will take me some time to wrap up it into a test case. I would realy
>>> like to help, but please let me know if it's realy necessary.
>>>
>>> On Oct 5, 2012 1:07 AM, "Eyal Wilde" <eyal@impactsoft.co.il
>>> <mailto:eyal@impactsoft.co.il>> wrote:
>>>
>>>
>>>                          ResultSetMetaData __md = __rs.getMetaData();
>>>       //this is fine
>>>                          String __sf1name = __md.getColumnName(1);
>>>        //this is fine
>>>                          int __if1type = __md.getColumnType(1);
>>>             //this is fine
>>>                          String __sf1type =
>>>     __md.getColumnTypeName(1); //this is SLOW!! ~15msec
>>>
>>>     postgres server version is 9.1.5
>>>     jdbc version, i checked both 9.1-901 and 9.1-903
>>>     and... BTW, 9.0-801 works good (while connected pg9.1)!
>>
>>
>> There does appear to be a change in the code that may have created the
>> slowness that you are observing. Please try a a test case in which two
>> back to back getColumnTypeName() calls are made. Is there a difference
>> in time between the first and second and is there still on the second
>> call a request to the postgres server.
>>
>> The code between 9.0-801 and later version does have a change in it
>> that looks like for the caching for field metadata through
>> fetchFieldMetaData().
>> That method is called in the later versions for getColumnTypeName()
>> with isAutoIncrement(), with was also added in later versions.
>>
>> danap.
>>
>>
>>
>>
>> --
>> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-jdbc

I'm sorry, but I'm a bit sleepy ...

I believe the code is more readable/better like this:

    public String getColumnTypeName(int column) throws SQLException
    {
        String type = getPGType(column);
        if ( ( "int4".equals(type) && isAutoIncrement(column)) {
            type = "serial";
        } else if ("int8".equals(type) && isAutoIncrement(column)) {
            type "bigserial";
        }

        return type;
    }

Luis Flores

On Wed, Oct 10, 2012 at 11:39 PM, Luis Flores
<luiscamposflores@gmail.com> wrote:
> I've looked at the AbstractJdbc2ResultSetMetadata getColumnTypeName,
> for version 1000 and 802.
>
> The change is simple, there is an extra call to isAutoIncrement, to be
> able to return correct values in the serial and bigserial cases.
>
> The isAutoIncrement call is slow because it triggers
> fetchFieldMetadata, witch get all metada for all fields.
>
> One simple optimization is to change the current method to:
>
>     public String getColumnTypeName(int column) throws SQLException
>     {
>         String type = getPGType(column);
>         if ( ( "int4".equals(type) || "int8".equals(type) ) &&
> isAutoIncrement(column)) {
>             if ("int4".equals(type)) {
>                 return "serial";
>             } else if ("int8".equals(type)) {
>                 return "bigserial";
>             }
>         }
>
>         return type;
>     }
>
> In this case, the isAutoIncrement is only called on int4 and int8
> columns, causing the performance for all the other column types to
> remain the same.
>
> May they are better options, but I think this change is good, it
> delays fetching metadata, and speeds up the method, without side
> effects.
>
> Luis Flores
>
> On Wed, Oct 10, 2012 at 11:10 PM, Luis Flores
> <luiscamposflores@gmail.com> wrote:
>> I also confirm the performance regression.
>>
>> Testing on Fedora17 64bit + PostgreSQL 9.1 + jdk 1.6.0_33, localhost
>> server, I get these numbers:
>> driver 802
>> getColumnType time (ms): 2777
>> getColumnTypeName time (ms): 1847
>> both time (ms): 1948
>>
>> driver 903
>> getColumnType time (ms): 3044
>> getColumnTypeName time (ms): 27123
>> both time (ms): 25142
>>
>> driver 1000
>> getColumnType time (ms): 2928
>> getColumnTypeName time (ms): 27214
>> both time (ms): 26407
>>
>> During the getColumnTypeName tests postgresql daemon used 100% cpu time.
>>
>> Here is the full test class:
>>
>> import java.sql.Connection;
>> import java.sql.DriverManager;
>> import java.sql.ResultSet;
>> import java.sql.ResultSetMetaData;
>> import java.sql.Statement;
>>
>>
>> public class Main
>> {
>>         public static void main( String arg[] )
>>                 throws Exception
>>         {
>>                 Class.forName( "org.postgresql.Driver" );
>>                 Connection conn = DriverManager.getConnection(
>> "jdbc:postgresql://localhost/test", "postgres", "" );
>>                 Statement create = conn.createStatement();
>>                 create.execute( "CREATE TABLE t(id SERIAL, name VARCHAR);" );
>>                 create.close();
>>                 long l = System.currentTimeMillis();
>>                 for( int i = 0; i < 10000; ++i )
>>                 {
>>                         Statement stm = conn.createStatement();
>>                         stm.execute( "SELECT id, name FROM t;" );
>>                         ResultSet rs = stm.getResultSet();
>>                         ResultSetMetaData rsmd = rs.getMetaData();
>>                         rsmd.getColumnType( 1 );
>> //                      rsmd.getColumnTypeName( 1 );
>>                         rs.close();
>>                         stm.close();
>>                 }
>>                 System.out.println( "getColumnType time (ms): " + (
>> System.currentTimeMillis() - l ) );
>>                 l = System.currentTimeMillis();
>>                 for( int i = 0; i < 10000; ++i )
>>                 {
>>                         Statement stm = conn.createStatement();
>>                         stm.execute( "SELECT id, name FROM t;" );
>>                         ResultSet rs = stm.getResultSet();
>>                         ResultSetMetaData rsmd = rs.getMetaData();
>> //                      rsmd.getColumnType( 1 );
>>                         rsmd.getColumnTypeName( 1 );
>>                         rs.close();
>>                         stm.close();
>>                 }
>>                 System.out.println( "getColumnTypeName time (ms): " + (
>> System.currentTimeMillis() - l ) );
>>                 l = System.currentTimeMillis();
>>                 for( int i = 0; i < 10000; ++i )
>>                 {
>>                         Statement stm = conn.createStatement();
>>                         stm.execute( "SELECT id, name FROM t;" );
>>                         ResultSet rs = stm.getResultSet();
>>                         ResultSetMetaData rsmd = rs.getMetaData();
>>                         rsmd.getColumnType( 1 );
>>                         rsmd.getColumnTypeName( 1 );
>>                         rs.close();
>>                         stm.close();
>>                 }
>>                 System.out.println( "both time (ms): " + ( System.currentTimeMillis() - l ) );
>>                 Statement drop = conn.createStatement();
>>                 drop.execute( "DROP TABLE t;" );
>>                 drop.close();
>>                 conn.close();
>>         }
>> }
>>
>>
>> Luis Flores
>>
>> On Tue, Oct 9, 2012 at 5:48 PM, dmp <danap@ttc-cmc.net> wrote:
>>> Eyal Wilde wrote:
>>>>
>>>> I verified with wireshark that  getColumnTypeName indeed do a request to
>>>> the postgres server. This happens with 9.1-901 and 9.1-903 BUT NOT WITH
>>>> 9.0-801!!
>>>>
>>>> So, the reason for the slowness seems to be obviouse now.
>>>>
>>>> it will take me some time to wrap up it into a test case. I would realy
>>>> like to help, but please let me know if it's realy necessary.
>>>>
>>>> On Oct 5, 2012 1:07 AM, "Eyal Wilde" <eyal@impactsoft.co.il
>>>> <mailto:eyal@impactsoft.co.il>> wrote:
>>>>
>>>>
>>>>                          ResultSetMetaData __md = __rs.getMetaData();
>>>>       //this is fine
>>>>                          String __sf1name = __md.getColumnName(1);
>>>>        //this is fine
>>>>                          int __if1type = __md.getColumnType(1);
>>>>             //this is fine
>>>>                          String __sf1type =
>>>>     __md.getColumnTypeName(1); //this is SLOW!! ~15msec
>>>>
>>>>     postgres server version is 9.1.5
>>>>     jdbc version, i checked both 9.1-901 and 9.1-903
>>>>     and... BTW, 9.0-801 works good (while connected pg9.1)!
>>>
>>>
>>> There does appear to be a change in the code that may have created the
>>> slowness that you are observing. Please try a a test case in which two
>>> back to back getColumnTypeName() calls are made. Is there a difference
>>> in time between the first and second and is there still on the second
>>> call a request to the postgres server.
>>>
>>> The code between 9.0-801 and later version does have a change in it
>>> that looks like for the caching for field metadata through
>>> fetchFieldMetaData().
>>> That method is called in the later versions for getColumnTypeName()
>>> with isAutoIncrement(), with was also added in later versions.
>>>
>>> danap.
>>>
>>>
>>>
>>>
>>> --
>>> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-jdbc

Luis Flores wrote:
> I've looked at the AbstractJdbc2ResultSetMetadata getColumnTypeName,
> for version 1000 and 802.
>
> The change is simple, there is an extra call to isAutoIncrement, to be
> able to return correct values in the serial and bigserial cases.
>
> The isAutoIncrement call is slow because it triggers
> fetchFieldMetadata, witch get all metada for all fields.
>
> One simple optimization is to change the current method to:
>
>      public String getColumnTypeName(int column) throws SQLException
>      {
>          String type = getPGType(column);
>          if ( ( "int4".equals(type) || "int8".equals(type) )&&
> isAutoIncrement(column)) {
>              if ("int4".equals(type)) {
>                  return "serial";
>              } else if ("int8".equals(type)) {
>                  return "bigserial";
>              }
>          }
>
>          return type;
>      }
>
> In this case, the isAutoIncrement is only called on int4 and int8
> columns, causing the performance for all the other column types to
> remain the same.
>
> May they are better options, but I think this change is good, it
> delays fetching metadata, and speeds up the method, without side
> effects.
>
> Luis Flores

isAutoIncrement() is not the only place that fetchFieldMetadata() is
called. isNullable(), getBaseColumnName(), getBaseSchemaName(), &
getBaseTableName(). Everyone of those calls is going to have the
same performance hit looks like the first time, the more table columns
the worst it gets. I know its nice to cache for all the table fields,
but when it hits performance like that you have to ask is it worth it.

ResultSetMetaData are usually limited creatures aren't they? Why would
you cache all your table fields info. Cache one by one maybe as called.

danap.

If i may... after we spent two days tracking this strange behavior, we decided to avoid getColumnTypeName at all. The optimization suggested would cause even more strange and un-solid performance, from the everage user's point of view.

I believe this extra info of serial/bigserial is not available as a result of optimization. so maybe it's time to ask the pg-server guys, to arrange something in thier area?

Regards.

I'm still checking the fetchFieldMetadata(), and I will try to
identify a way of getting the serial info without the full penalty we
are having, or to understand if loading metadata one field at a time
is much slower than the current method.

And I understand, and agree, it is strange to have irregular performance.

But, for now, we can understand and justify the need to call
isAutoIncrement in int4 and int8 columns, so that we detect if they
are in reality serial columns. We can't justify the isAutoIncrement
call in all other cases. Aside from the huge performance hit, it's the
wrong algorithm, we shouldn't call a method if we don't need the
answer.

The irregular performance comes from added complexity for some data
types, not from a buggy algorithm. To me it seems it would be easier
to understand the performance hit cause if it only happened on serial
columns.


Luis Flores

On Thu, Oct 11, 2012 at 5:12 AM, Eyal Wilde <eyal.wilde@gmail.com> wrote:
> If i may... after we spent two days tracking this strange behavior, we
> decided to avoid getColumnTypeName at all. The optimization suggested would
> cause even more strange and un-solid performance, from the everage user's
> point of view.
>
> I believe this extra info of serial/bigserial is not available as a result
> of optimization. so maybe it's time to ask the pg-server guys, to arrange
> something in thier area?
>
> Regards.
>
> On Oct 11, 2012 12:46 AM, "Luis Flores" <luiscamposflores@gmail.com> wrote:
>>
>> I'm sorry, but I'm a bit sleepy ...
>>
>> I believe the code is more readable/better like this:
>>
>>     public String getColumnTypeName(int column) throws SQLException
>>     {
>>         String type = getPGType(column);
>>         if ( ( "int4".equals(type) && isAutoIncrement(column)) {
>>             type = "serial";
>>         } else if ("int8".equals(type) && isAutoIncrement(column)) {
>>             type "bigserial";
>>         }
>>
>>         return type;
>>     }
>>
>> Luis Flores
>>
>> On Wed, Oct 10, 2012 at 11:39 PM, Luis Flores
>> <luiscamposflores@gmail.com> wrote:
>> > I've looked at the AbstractJdbc2ResultSetMetadata getColumnTypeName,
>> > for version 1000 and 802.
>> >
>> > The change is simple, there is an extra call to isAutoIncrement, to be
>> > able to return correct values in the serial and bigserial cases.
>> >
>> > The isAutoIncrement call is slow because it triggers
>> > fetchFieldMetadata, witch get all metada for all fields.
>> >
>> > One simple optimization is to change the current method to:
>> >
>> >     public String getColumnTypeName(int column) throws SQLException
>> >     {
>> >         String type = getPGType(column);
>> >         if ( ( "int4".equals(type) || "int8".equals(type) ) &&
>> > isAutoIncrement(column)) {
>> >             if ("int4".equals(type)) {
>> >                 return "serial";
>> >             } else if ("int8".equals(type)) {
>> >                 return "bigserial";
>> >             }
>> >         }
>> >
>> >         return type;
>> >     }
>> >
>> > In this case, the isAutoIncrement is only called on int4 and int8
>> > columns, causing the performance for all the other column types to
>> > remain the same.
>> >
>> > May they are better options, but I think this change is good, it
>> > delays fetching metadata, and speeds up the method, without side
>> > effects.
>> >
>> > Luis Flores
>> >
>> > On Wed, Oct 10, 2012 at 11:10 PM, Luis Flores
>> > <luiscamposflores@gmail.com> wrote:
>> >> I also confirm the performance regression.
>> >>
>> >> Testing on Fedora17 64bit + PostgreSQL 9.1 + jdk 1.6.0_33, localhost
>> >> server, I get these numbers:
>> >> driver 802
>> >> getColumnType time (ms): 2777
>> >> getColumnTypeName time (ms): 1847
>> >> both time (ms): 1948
>> >>
>> >> driver 903
>> >> getColumnType time (ms): 3044
>> >> getColumnTypeName time (ms): 27123
>> >> both time (ms): 25142
>> >>
>> >> driver 1000
>> >> getColumnType time (ms): 2928
>> >> getColumnTypeName time (ms): 27214
>> >> both time (ms): 26407
>> >>
>> >> During the getColumnTypeName tests postgresql daemon used 100% cpu
>> >> time.
>> >>
>> >> Here is the full test class:
>> >>
>> >> import java.sql.Connection;
>> >> import java.sql.DriverManager;
>> >> import java.sql.ResultSet;
>> >> import java.sql.ResultSetMetaData;
>> >> import java.sql.Statement;
>> >>
>> >>
>> >> public class Main
>> >> {
>> >>         public static void main( String arg[] )
>> >>                 throws Exception
>> >>         {
>> >>                 Class.forName( "org.postgresql.Driver" );
>> >>                 Connection conn = DriverManager.getConnection(
>> >> "jdbc:postgresql://localhost/test", "postgres", "" );
>> >>                 Statement create = conn.createStatement();
>> >>                 create.execute( "CREATE TABLE t(id SERIAL, name
>> >> VARCHAR);" );
>> >>                 create.close();
>> >>                 long l = System.currentTimeMillis();
>> >>                 for( int i = 0; i < 10000; ++i )
>> >>                 {
>> >>                         Statement stm = conn.createStatement();
>> >>                         stm.execute( "SELECT id, name FROM t;" );
>> >>                         ResultSet rs = stm.getResultSet();
>> >>                         ResultSetMetaData rsmd = rs.getMetaData();
>> >>                         rsmd.getColumnType( 1 );
>> >> //                      rsmd.getColumnTypeName( 1 );
>> >>                         rs.close();
>> >>                         stm.close();
>> >>                 }
>> >>                 System.out.println( "getColumnType time (ms): " + (
>> >> System.currentTimeMillis() - l ) );
>> >>                 l = System.currentTimeMillis();
>> >>                 for( int i = 0; i < 10000; ++i )
>> >>                 {
>> >>                         Statement stm = conn.createStatement();
>> >>                         stm.execute( "SELECT id, name FROM t;" );
>> >>                         ResultSet rs = stm.getResultSet();
>> >>                         ResultSetMetaData rsmd = rs.getMetaData();
>> >> //                      rsmd.getColumnType( 1 );
>> >>                         rsmd.getColumnTypeName( 1 );
>> >>                         rs.close();
>> >>                         stm.close();
>> >>                 }
>> >>                 System.out.println( "getColumnTypeName time (ms): " + (
>> >> System.currentTimeMillis() - l ) );
>> >>                 l = System.currentTimeMillis();
>> >>                 for( int i = 0; i < 10000; ++i )
>> >>                 {
>> >>                         Statement stm = conn.createStatement();
>> >>                         stm.execute( "SELECT id, name FROM t;" );
>> >>                         ResultSet rs = stm.getResultSet();
>> >>                         ResultSetMetaData rsmd = rs.getMetaData();
>> >>                         rsmd.getColumnType( 1 );
>> >>                         rsmd.getColumnTypeName( 1 );
>> >>                         rs.close();
>> >>                         stm.close();
>> >>                 }
>> >>                 System.out.println( "both time (ms): " + (
>> >> System.currentTimeMillis() - l ) );
>> >>                 Statement drop = conn.createStatement();
>> >>                 drop.execute( "DROP TABLE t;" );
>> >>                 drop.close();
>> >>                 conn.close();
>> >>         }
>> >> }
>> >>
>> >>
>> >> Luis Flores
>> >>
>> >> On Tue, Oct 9, 2012 at 5:48 PM, dmp <danap@ttc-cmc.net> wrote:
>> >>> Eyal Wilde wrote:
>> >>>>
>> >>>> I verified with wireshark that  getColumnTypeName indeed do a request
>> >>>> to
>> >>>> the postgres server. This happens with 9.1-901 and 9.1-903 BUT NOT
>> >>>> WITH
>> >>>> 9.0-801!!
>> >>>>
>> >>>> So, the reason for the slowness seems to be obviouse now.
>> >>>>
>> >>>> it will take me some time to wrap up it into a test case. I would
>> >>>> realy
>> >>>> like to help, but please let me know if it's realy necessary.
>> >>>>
>> >>>> On Oct 5, 2012 1:07 AM, "Eyal Wilde" <eyal@impactsoft.co.il
>> >>>> <mailto:eyal@impactsoft.co.il>> wrote:
>> >>>>
>> >>>>
>> >>>>                          ResultSetMetaData __md = __rs.getMetaData();
>> >>>>       //this is fine
>> >>>>                          String __sf1name = __md.getColumnName(1);
>> >>>>        //this is fine
>> >>>>                          int __if1type = __md.getColumnType(1);
>> >>>>             //this is fine
>> >>>>                          String __sf1type =
>> >>>>     __md.getColumnTypeName(1); //this is SLOW!! ~15msec
>> >>>>
>> >>>>     postgres server version is 9.1.5
>> >>>>     jdbc version, i checked both 9.1-901 and 9.1-903
>> >>>>     and... BTW, 9.0-801 works good (while connected pg9.1)!
>> >>>
>> >>>
>> >>> There does appear to be a change in the code that may have created the
>> >>> slowness that you are observing. Please try a a test case in which two
>> >>> back to back getColumnTypeName() calls are made. Is there a difference
>> >>> in time between the first and second and is there still on the second
>> >>> call a request to the postgres server.
>> >>>
>> >>> The code between 9.0-801 and later version does have a change in it
>> >>> that looks like for the caching for field metadata through
>> >>> fetchFieldMetaData().
>> >>> That method is called in the later versions for getColumnTypeName()
>> >>> with isAutoIncrement(), with was also added in later versions.
>> >>>
>> >>> danap.
>> >>>
>> >>>
>> >>>
>> >>>
>> >>> --
>> >>> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
>> >>> To make changes to your subscription:
>> >>> http://www.postgresql.org/mailpref/pgsql-jdbc

What about a field that concatenates the serial value with a varchar to generate an auto-incrementing but non-numeric
primarykey?  How should that be classified? 

David J.

On Oct 11, 2012, at 3:55, Luis Flores <luiscamposflores@gmail.com> wrote:

> I'm still checking the fetchFieldMetadata(), and I will try to
> identify a way of getting the serial info without the full penalty we
> are having, or to understand if loading metadata one field at a time
> is much slower than the current method.
>
> And I understand, and agree, it is strange to have irregular performance.
>
> But, for now, we can understand and justify the need to call
> isAutoIncrement in int4 and int8 columns, so that we detect if they
> are in reality serial columns. We can't justify the isAutoIncrement
> call in all other cases. Aside from the huge performance hit, it's the
> wrong algorithm, we shouldn't call a method if we don't need the
> answer.
>
> The irregular performance comes from added complexity for some data
> types, not from a buggy algorithm. To me it seems it would be easier
> to understand the performance hit cause if it only happened on serial
> columns.
>
>
> Luis Flores
>
> On Thu, Oct 11, 2012 at 5:12 AM, Eyal Wilde <eyal.wilde@gmail.com> wrote:
>> If i may... after we spent two days tracking this strange behavior, we
>> decided to avoid getColumnTypeName at all. The optimization suggested would
>> cause even more strange and un-solid performance, from the everage user's
>> point of view.
>>
>> I believe this extra info of serial/bigserial is not available as a result
>> of optimization. so maybe it's time to ask the pg-server guys, to arrange
>> something in thier area?
>>
>> Regards.
>>
>> On Oct 11, 2012 12:46 AM, "Luis Flores" <luiscamposflores@gmail.com> wrote:
>>>
>>> I'm sorry, but I'm a bit sleepy ...
>>>
>>> I believe the code is more readable/better like this:
>>>
>>>    public String getColumnTypeName(int column) throws SQLException
>>>    {
>>>        String type = getPGType(column);
>>>        if ( ( "int4".equals(type) && isAutoIncrement(column)) {
>>>            type = "serial";
>>>        } else if ("int8".equals(type) && isAutoIncrement(column)) {
>>>            type "bigserial";
>>>        }
>>>
>>>        return type;
>>>    }
>>>
>>> Luis Flores
>>>
>>> On Wed, Oct 10, 2012 at 11:39 PM, Luis Flores
>>> <luiscamposflores@gmail.com> wrote:
>>>> I've looked at the AbstractJdbc2ResultSetMetadata getColumnTypeName,
>>>> for version 1000 and 802.
>>>>
>>>> The change is simple, there is an extra call to isAutoIncrement, to be
>>>> able to return correct values in the serial and bigserial cases.
>>>>
>>>> The isAutoIncrement call is slow because it triggers
>>>> fetchFieldMetadata, witch get all metada for all fields.
>>>>
>>>> One simple optimization is to change the current method to:
>>>>
>>>>    public String getColumnTypeName(int column) throws SQLException
>>>>    {
>>>>        String type = getPGType(column);
>>>>        if ( ( "int4".equals(type) || "int8".equals(type) ) &&
>>>> isAutoIncrement(column)) {
>>>>            if ("int4".equals(type)) {
>>>>                return "serial";
>>>>            } else if ("int8".equals(type)) {
>>>>                return "bigserial";
>>>>            }
>>>>        }
>>>>
>>>>        return type;
>>>>    }
>>>>
>>>> In this case, the isAutoIncrement is only called on int4 and int8
>>>> columns, causing the performance for all the other column types to
>>>> remain the same.
>>>>
>>>> May they are better options, but I think this change is good, it
>>>> delays fetching metadata, and speeds up the method, without side
>>>> effects.
>>>>
>>>> Luis Flores
>>>>
>>>> On Wed, Oct 10, 2012 at 11:10 PM, Luis Flores
>>>> <luiscamposflores@gmail.com> wrote:
>>>>> I also confirm the performance regression.
>>>>>
>>>>> Testing on Fedora17 64bit + PostgreSQL 9.1 + jdk 1.6.0_33, localhost
>>>>> server, I get these numbers:
>>>>> driver 802
>>>>> getColumnType time (ms): 2777
>>>>> getColumnTypeName time (ms): 1847
>>>>> both time (ms): 1948
>>>>>
>>>>> driver 903
>>>>> getColumnType time (ms): 3044
>>>>> getColumnTypeName time (ms): 27123
>>>>> both time (ms): 25142
>>>>>
>>>>> driver 1000
>>>>> getColumnType time (ms): 2928
>>>>> getColumnTypeName time (ms): 27214
>>>>> both time (ms): 26407
>>>>>
>>>>> During the getColumnTypeName tests postgresql daemon used 100% cpu
>>>>> time.
>>>>>
>>>>> Here is the full test class:
>>>>>
>>>>> import java.sql.Connection;
>>>>> import java.sql.DriverManager;
>>>>> import java.sql.ResultSet;
>>>>> import java.sql.ResultSetMetaData;
>>>>> import java.sql.Statement;
>>>>>
>>>>>
>>>>> public class Main
>>>>> {
>>>>>        public static void main( String arg[] )
>>>>>                throws Exception
>>>>>        {
>>>>>                Class.forName( "org.postgresql.Driver" );
>>>>>                Connection conn = DriverManager.getConnection(
>>>>> "jdbc:postgresql://localhost/test", "postgres", "" );
>>>>>                Statement create = conn.createStatement();
>>>>>                create.execute( "CREATE TABLE t(id SERIAL, name
>>>>> VARCHAR);" );
>>>>>                create.close();
>>>>>                long l = System.currentTimeMillis();
>>>>>                for( int i = 0; i < 10000; ++i )
>>>>>                {
>>>>>                        Statement stm = conn.createStatement();
>>>>>                        stm.execute( "SELECT id, name FROM t;" );
>>>>>                        ResultSet rs = stm.getResultSet();
>>>>>                        ResultSetMetaData rsmd = rs.getMetaData();
>>>>>                        rsmd.getColumnType( 1 );
>>>>> //                      rsmd.getColumnTypeName( 1 );
>>>>>                        rs.close();
>>>>>                        stm.close();
>>>>>                }
>>>>>                System.out.println( "getColumnType time (ms): " + (
>>>>> System.currentTimeMillis() - l ) );
>>>>>                l = System.currentTimeMillis();
>>>>>                for( int i = 0; i < 10000; ++i )
>>>>>                {
>>>>>                        Statement stm = conn.createStatement();
>>>>>                        stm.execute( "SELECT id, name FROM t;" );
>>>>>                        ResultSet rs = stm.getResultSet();
>>>>>                        ResultSetMetaData rsmd = rs.getMetaData();
>>>>> //                      rsmd.getColumnType( 1 );
>>>>>                        rsmd.getColumnTypeName( 1 );
>>>>>                        rs.close();
>>>>>                        stm.close();
>>>>>                }
>>>>>                System.out.println( "getColumnTypeName time (ms): " + (
>>>>> System.currentTimeMillis() - l ) );
>>>>>                l = System.currentTimeMillis();
>>>>>                for( int i = 0; i < 10000; ++i )
>>>>>                {
>>>>>                        Statement stm = conn.createStatement();
>>>>>                        stm.execute( "SELECT id, name FROM t;" );
>>>>>                        ResultSet rs = stm.getResultSet();
>>>>>                        ResultSetMetaData rsmd = rs.getMetaData();
>>>>>                        rsmd.getColumnType( 1 );
>>>>>                        rsmd.getColumnTypeName( 1 );
>>>>>                        rs.close();
>>>>>                        stm.close();
>>>>>                }
>>>>>                System.out.println( "both time (ms): " + (
>>>>> System.currentTimeMillis() - l ) );
>>>>>                Statement drop = conn.createStatement();
>>>>>                drop.execute( "DROP TABLE t;" );
>>>>>                drop.close();
>>>>>                conn.close();
>>>>>        }
>>>>> }
>>>>>
>>>>>
>>>>> Luis Flores
>>>>>
>>>>> On Tue, Oct 9, 2012 at 5:48 PM, dmp <danap@ttc-cmc.net> wrote:
>>>>>> Eyal Wilde wrote:
>>>>>>>
>>>>>>> I verified with wireshark that  getColumnTypeName indeed do a request
>>>>>>> to
>>>>>>> the postgres server. This happens with 9.1-901 and 9.1-903 BUT NOT
>>>>>>> WITH
>>>>>>> 9.0-801!!
>>>>>>>
>>>>>>> So, the reason for the slowness seems to be obviouse now.
>>>>>>>
>>>>>>> it will take me some time to wrap up it into a test case. I would
>>>>>>> realy
>>>>>>> like to help, but please let me know if it's realy necessary.
>>>>>>>
>>>>>>> On Oct 5, 2012 1:07 AM, "Eyal Wilde" <eyal@impactsoft.co.il
>>>>>>> <mailto:eyal@impactsoft.co.il>> wrote:
>>>>>>>
>>>>>>>
>>>>>>>                         ResultSetMetaData __md = __rs.getMetaData();
>>>>>>>      //this is fine
>>>>>>>                         String __sf1name = __md.getColumnName(1);
>>>>>>>       //this is fine
>>>>>>>                         int __if1type = __md.getColumnType(1);
>>>>>>>            //this is fine
>>>>>>>                         String __sf1type =
>>>>>>>    __md.getColumnTypeName(1); //this is SLOW!! ~15msec
>>>>>>>
>>>>>>>    postgres server version is 9.1.5
>>>>>>>    jdbc version, i checked both 9.1-901 and 9.1-903
>>>>>>>    and... BTW, 9.0-801 works good (while connected pg9.1)!
>>>>>>
>>>>>>
>>>>>> There does appear to be a change in the code that may have created the
>>>>>> slowness that you are observing. Please try a a test case in which two
>>>>>> back to back getColumnTypeName() calls are made. Is there a difference
>>>>>> in time between the first and second and is there still on the second
>>>>>> call a request to the postgres server.
>>>>>>
>>>>>> The code between 9.0-801 and later version does have a change in it
>>>>>> that looks like for the caching for field metadata through
>>>>>> fetchFieldMetaData().
>>>>>> That method is called in the later versions for getColumnTypeName()
>>>>>> with isAutoIncrement(), with was also added in later versions.
>>>>>>
>>>>>> danap.
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
>>>>>> To make changes to your subscription:
>>>>>> http://www.postgresql.org/mailpref/pgsql-jdbc
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc

David,

I don't think we can determine that from the server meta data, so I
would think the user is on their own in that case.

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


On Thu, Oct 11, 2012 at 9:34 AM, David Johnston <polobo@yahoo.com> wrote:
> What about a field that concatenates the serial value with a varchar to generate an auto-incrementing but non-numeric
primarykey?  How should that be classified? 
>
> David J.
>
> On Oct 11, 2012, at 3:55, Luis Flores <luiscamposflores@gmail.com> wrote:
>
>> I'm still checking the fetchFieldMetadata(), and I will try to
>> identify a way of getting the serial info without the full penalty we
>> are having, or to understand if loading metadata one field at a time
>> is much slower than the current method.
>>
>> And I understand, and agree, it is strange to have irregular performance.
>>
>> But, for now, we can understand and justify the need to call
>> isAutoIncrement in int4 and int8 columns, so that we detect if they
>> are in reality serial columns. We can't justify the isAutoIncrement
>> call in all other cases. Aside from the huge performance hit, it's the
>> wrong algorithm, we shouldn't call a method if we don't need the
>> answer.
>>
>> The irregular performance comes from added complexity for some data
>> types, not from a buggy algorithm. To me it seems it would be easier
>> to understand the performance hit cause if it only happened on serial
>> columns.
>>
>>
>> Luis Flores
>>
>> On Thu, Oct 11, 2012 at 5:12 AM, Eyal Wilde <eyal.wilde@gmail.com> wrote:
>>> If i may... after we spent two days tracking this strange behavior, we
>>> decided to avoid getColumnTypeName at all. The optimization suggested would
>>> cause even more strange and un-solid performance, from the everage user's
>>> point of view.
>>>
>>> I believe this extra info of serial/bigserial is not available as a result
>>> of optimization. so maybe it's time to ask the pg-server guys, to arrange
>>> something in thier area?
>>>
>>> Regards.
>>>
>>> On Oct 11, 2012 12:46 AM, "Luis Flores" <luiscamposflores@gmail.com> wrote:
>>>>
>>>> I'm sorry, but I'm a bit sleepy ...
>>>>
>>>> I believe the code is more readable/better like this:
>>>>
>>>>    public String getColumnTypeName(int column) throws SQLException
>>>>    {
>>>>        String type = getPGType(column);
>>>>        if ( ( "int4".equals(type) && isAutoIncrement(column)) {
>>>>            type = "serial";
>>>>        } else if ("int8".equals(type) && isAutoIncrement(column)) {
>>>>            type "bigserial";
>>>>        }
>>>>
>>>>        return type;
>>>>    }
>>>>
>>>> Luis Flores
>>>>
>>>> On Wed, Oct 10, 2012 at 11:39 PM, Luis Flores
>>>> <luiscamposflores@gmail.com> wrote:
>>>>> I've looked at the AbstractJdbc2ResultSetMetadata getColumnTypeName,
>>>>> for version 1000 and 802.
>>>>>
>>>>> The change is simple, there is an extra call to isAutoIncrement, to be
>>>>> able to return correct values in the serial and bigserial cases.
>>>>>
>>>>> The isAutoIncrement call is slow because it triggers
>>>>> fetchFieldMetadata, witch get all metada for all fields.
>>>>>
>>>>> One simple optimization is to change the current method to:
>>>>>
>>>>>    public String getColumnTypeName(int column) throws SQLException
>>>>>    {
>>>>>        String type = getPGType(column);
>>>>>        if ( ( "int4".equals(type) || "int8".equals(type) ) &&
>>>>> isAutoIncrement(column)) {
>>>>>            if ("int4".equals(type)) {
>>>>>                return "serial";
>>>>>            } else if ("int8".equals(type)) {
>>>>>                return "bigserial";
>>>>>            }
>>>>>        }
>>>>>
>>>>>        return type;
>>>>>    }
>>>>>
>>>>> In this case, the isAutoIncrement is only called on int4 and int8
>>>>> columns, causing the performance for all the other column types to
>>>>> remain the same.
>>>>>
>>>>> May they are better options, but I think this change is good, it
>>>>> delays fetching metadata, and speeds up the method, without side
>>>>> effects.
>>>>>
>>>>> Luis Flores
>>>>>
>>>>> On Wed, Oct 10, 2012 at 11:10 PM, Luis Flores
>>>>> <luiscamposflores@gmail.com> wrote:
>>>>>> I also confirm the performance regression.
>>>>>>
>>>>>> Testing on Fedora17 64bit + PostgreSQL 9.1 + jdk 1.6.0_33, localhost
>>>>>> server, I get these numbers:
>>>>>> driver 802
>>>>>> getColumnType time (ms): 2777
>>>>>> getColumnTypeName time (ms): 1847
>>>>>> both time (ms): 1948
>>>>>>
>>>>>> driver 903
>>>>>> getColumnType time (ms): 3044
>>>>>> getColumnTypeName time (ms): 27123
>>>>>> both time (ms): 25142
>>>>>>
>>>>>> driver 1000
>>>>>> getColumnType time (ms): 2928
>>>>>> getColumnTypeName time (ms): 27214
>>>>>> both time (ms): 26407
>>>>>>
>>>>>> During the getColumnTypeName tests postgresql daemon used 100% cpu
>>>>>> time.
>>>>>>
>>>>>> Here is the full test class:
>>>>>>
>>>>>> import java.sql.Connection;
>>>>>> import java.sql.DriverManager;
>>>>>> import java.sql.ResultSet;
>>>>>> import java.sql.ResultSetMetaData;
>>>>>> import java.sql.Statement;
>>>>>>
>>>>>>
>>>>>> public class Main
>>>>>> {
>>>>>>        public static void main( String arg[] )
>>>>>>                throws Exception
>>>>>>        {
>>>>>>                Class.forName( "org.postgresql.Driver" );
>>>>>>                Connection conn = DriverManager.getConnection(
>>>>>> "jdbc:postgresql://localhost/test", "postgres", "" );
>>>>>>                Statement create = conn.createStatement();
>>>>>>                create.execute( "CREATE TABLE t(id SERIAL, name
>>>>>> VARCHAR);" );
>>>>>>                create.close();
>>>>>>                long l = System.currentTimeMillis();
>>>>>>                for( int i = 0; i < 10000; ++i )
>>>>>>                {
>>>>>>                        Statement stm = conn.createStatement();
>>>>>>                        stm.execute( "SELECT id, name FROM t;" );
>>>>>>                        ResultSet rs = stm.getResultSet();
>>>>>>                        ResultSetMetaData rsmd = rs.getMetaData();
>>>>>>                        rsmd.getColumnType( 1 );
>>>>>> //                      rsmd.getColumnTypeName( 1 );
>>>>>>                        rs.close();
>>>>>>                        stm.close();
>>>>>>                }
>>>>>>                System.out.println( "getColumnType time (ms): " + (
>>>>>> System.currentTimeMillis() - l ) );
>>>>>>                l = System.currentTimeMillis();
>>>>>>                for( int i = 0; i < 10000; ++i )
>>>>>>                {
>>>>>>                        Statement stm = conn.createStatement();
>>>>>>                        stm.execute( "SELECT id, name FROM t;" );
>>>>>>                        ResultSet rs = stm.getResultSet();
>>>>>>                        ResultSetMetaData rsmd = rs.getMetaData();
>>>>>> //                      rsmd.getColumnType( 1 );
>>>>>>                        rsmd.getColumnTypeName( 1 );
>>>>>>                        rs.close();
>>>>>>                        stm.close();
>>>>>>                }
>>>>>>                System.out.println( "getColumnTypeName time (ms): " + (
>>>>>> System.currentTimeMillis() - l ) );
>>>>>>                l = System.currentTimeMillis();
>>>>>>                for( int i = 0; i < 10000; ++i )
>>>>>>                {
>>>>>>                        Statement stm = conn.createStatement();
>>>>>>                        stm.execute( "SELECT id, name FROM t;" );
>>>>>>                        ResultSet rs = stm.getResultSet();
>>>>>>                        ResultSetMetaData rsmd = rs.getMetaData();
>>>>>>                        rsmd.getColumnType( 1 );
>>>>>>                        rsmd.getColumnTypeName( 1 );
>>>>>>                        rs.close();
>>>>>>                        stm.close();
>>>>>>                }
>>>>>>                System.out.println( "both time (ms): " + (
>>>>>> System.currentTimeMillis() - l ) );
>>>>>>                Statement drop = conn.createStatement();
>>>>>>                drop.execute( "DROP TABLE t;" );
>>>>>>                drop.close();
>>>>>>                conn.close();
>>>>>>        }
>>>>>> }
>>>>>>
>>>>>>
>>>>>> Luis Flores
>>>>>>
>>>>>> On Tue, Oct 9, 2012 at 5:48 PM, dmp <danap@ttc-cmc.net> wrote:
>>>>>>> Eyal Wilde wrote:
>>>>>>>>
>>>>>>>> I verified with wireshark that  getColumnTypeName indeed do a request
>>>>>>>> to
>>>>>>>> the postgres server. This happens with 9.1-901 and 9.1-903 BUT NOT
>>>>>>>> WITH
>>>>>>>> 9.0-801!!
>>>>>>>>
>>>>>>>> So, the reason for the slowness seems to be obviouse now.
>>>>>>>>
>>>>>>>> it will take me some time to wrap up it into a test case. I would
>>>>>>>> realy
>>>>>>>> like to help, but please let me know if it's realy necessary.
>>>>>>>>
>>>>>>>> On Oct 5, 2012 1:07 AM, "Eyal Wilde" <eyal@impactsoft.co.il
>>>>>>>> <mailto:eyal@impactsoft.co.il>> wrote:
>>>>>>>>
>>>>>>>>
>>>>>>>>                         ResultSetMetaData __md = __rs.getMetaData();
>>>>>>>>      //this is fine
>>>>>>>>                         String __sf1name = __md.getColumnName(1);
>>>>>>>>       //this is fine
>>>>>>>>                         int __if1type = __md.getColumnType(1);
>>>>>>>>            //this is fine
>>>>>>>>                         String __sf1type =
>>>>>>>>    __md.getColumnTypeName(1); //this is SLOW!! ~15msec
>>>>>>>>
>>>>>>>>    postgres server version is 9.1.5
>>>>>>>>    jdbc version, i checked both 9.1-901 and 9.1-903
>>>>>>>>    and... BTW, 9.0-801 works good (while connected pg9.1)!
>>>>>>>
>>>>>>>
>>>>>>> There does appear to be a change in the code that may have created the
>>>>>>> slowness that you are observing. Please try a a test case in which two
>>>>>>> back to back getColumnTypeName() calls are made. Is there a difference
>>>>>>> in time between the first and second and is there still on the second
>>>>>>> call a request to the postgres server.
>>>>>>>
>>>>>>> The code between 9.0-801 and later version does have a change in it
>>>>>>> that looks like for the caching for field metadata through
>>>>>>> fetchFieldMetaData().
>>>>>>> That method is called in the later versions for getColumnTypeName()
>>>>>>> with isAutoIncrement(), with was also added in later versions.
>>>>>>>
>>>>>>> danap.
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
>>>>>>> To make changes to your subscription:
>>>>>>> http://www.postgresql.org/mailpref/pgsql-jdbc
>>
>>
>> --
>> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-jdbc
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc

You are talking about a different matter.
1. 802 and prior drivers named serials as int4 (and big serials int8)
2. 900 and newer drivers named serials as serials (and you got a
performance hit from that)

We are now attempting to solve the problem #2, maintain the correct
column name (serial and big serial), and if possible improve
performance.

If you use a column with a default value from a sequence, the column
type name doesn't change. If the column is int AND is generated from a
sequence called <TABLE>_<COLUMN>_seq, then in postgresql (you can
check in pgadmin) the column type name name changes from integer to
serial. We may or may not agree with this, but it's the way postgresql
works, so, the jdbc driver correctness and naming should be in line
with the postgresql server naming (mapping to jdbc type naming).

The change I proposed maintain exactly the same results of the current
driver (900 and newer), but only improves the performance when there
are no int4 or int8 columns in the result set.

Luis Flores

On Thu, Oct 11, 2012 at 2:34 PM, David Johnston <polobo@yahoo.com> wrote:
> What about a field that concatenates the serial value with a varchar to generate an auto-incrementing but non-numeric
primarykey?  How should that be classified? 
>
> David J.
>
> On Oct 11, 2012, at 3:55, Luis Flores <luiscamposflores@gmail.com> wrote:
>
>> I'm still checking the fetchFieldMetadata(), and I will try to
>> identify a way of getting the serial info without the full penalty we
>> are having, or to understand if loading metadata one field at a time
>> is much slower than the current method.
>>
>> And I understand, and agree, it is strange to have irregular performance.
>>
>> But, for now, we can understand and justify the need to call
>> isAutoIncrement in int4 and int8 columns, so that we detect if they
>> are in reality serial columns. We can't justify the isAutoIncrement
>> call in all other cases. Aside from the huge performance hit, it's the
>> wrong algorithm, we shouldn't call a method if we don't need the
>> answer.
>>
>> The irregular performance comes from added complexity for some data
>> types, not from a buggy algorithm. To me it seems it would be easier
>> to understand the performance hit cause if it only happened on serial
>> columns.
>>
>>
>> Luis Flores
>>
>> On Thu, Oct 11, 2012 at 5:12 AM, Eyal Wilde <eyal.wilde@gmail.com> wrote:
>>> If i may... after we spent two days tracking this strange behavior, we
>>> decided to avoid getColumnTypeName at all. The optimization suggested would
>>> cause even more strange and un-solid performance, from the everage user's
>>> point of view.
>>>
>>> I believe this extra info of serial/bigserial is not available as a result
>>> of optimization. so maybe it's time to ask the pg-server guys, to arrange
>>> something in thier area?
>>>
>>> Regards.
>>>
>>> On Oct 11, 2012 12:46 AM, "Luis Flores" <luiscamposflores@gmail.com> wrote:
>>>>
>>>> I'm sorry, but I'm a bit sleepy ...
>>>>
>>>> I believe the code is more readable/better like this:
>>>>
>>>>    public String getColumnTypeName(int column) throws SQLException
>>>>    {
>>>>        String type = getPGType(column);
>>>>        if ( ( "int4".equals(type) && isAutoIncrement(column)) {
>>>>            type = "serial";
>>>>        } else if ("int8".equals(type) && isAutoIncrement(column)) {
>>>>            type "bigserial";
>>>>        }
>>>>
>>>>        return type;
>>>>    }
>>>>
>>>> Luis Flores
>>>>
>>>> On Wed, Oct 10, 2012 at 11:39 PM, Luis Flores
>>>> <luiscamposflores@gmail.com> wrote:
>>>>> I've looked at the AbstractJdbc2ResultSetMetadata getColumnTypeName,
>>>>> for version 1000 and 802.
>>>>>
>>>>> The change is simple, there is an extra call to isAutoIncrement, to be
>>>>> able to return correct values in the serial and bigserial cases.
>>>>>
>>>>> The isAutoIncrement call is slow because it triggers
>>>>> fetchFieldMetadata, witch get all metada for all fields.
>>>>>
>>>>> One simple optimization is to change the current method to:
>>>>>
>>>>>    public String getColumnTypeName(int column) throws SQLException
>>>>>    {
>>>>>        String type = getPGType(column);
>>>>>        if ( ( "int4".equals(type) || "int8".equals(type) ) &&
>>>>> isAutoIncrement(column)) {
>>>>>            if ("int4".equals(type)) {
>>>>>                return "serial";
>>>>>            } else if ("int8".equals(type)) {
>>>>>                return "bigserial";
>>>>>            }
>>>>>        }
>>>>>
>>>>>        return type;
>>>>>    }
>>>>>
>>>>> In this case, the isAutoIncrement is only called on int4 and int8
>>>>> columns, causing the performance for all the other column types to
>>>>> remain the same.
>>>>>
>>>>> May they are better options, but I think this change is good, it
>>>>> delays fetching metadata, and speeds up the method, without side
>>>>> effects.
>>>>>
>>>>> Luis Flores
>>>>>
>>>>> On Wed, Oct 10, 2012 at 11:10 PM, Luis Flores
>>>>> <luiscamposflores@gmail.com> wrote:
>>>>>> I also confirm the performance regression.
>>>>>>
>>>>>> Testing on Fedora17 64bit + PostgreSQL 9.1 + jdk 1.6.0_33, localhost
>>>>>> server, I get these numbers:
>>>>>> driver 802
>>>>>> getColumnType time (ms): 2777
>>>>>> getColumnTypeName time (ms): 1847
>>>>>> both time (ms): 1948
>>>>>>
>>>>>> driver 903
>>>>>> getColumnType time (ms): 3044
>>>>>> getColumnTypeName time (ms): 27123
>>>>>> both time (ms): 25142
>>>>>>
>>>>>> driver 1000
>>>>>> getColumnType time (ms): 2928
>>>>>> getColumnTypeName time (ms): 27214
>>>>>> both time (ms): 26407
>>>>>>
>>>>>> During the getColumnTypeName tests postgresql daemon used 100% cpu
>>>>>> time.
>>>>>>
>>>>>> Here is the full test class:
>>>>>>
>>>>>> import java.sql.Connection;
>>>>>> import java.sql.DriverManager;
>>>>>> import java.sql.ResultSet;
>>>>>> import java.sql.ResultSetMetaData;
>>>>>> import java.sql.Statement;
>>>>>>
>>>>>>
>>>>>> public class Main
>>>>>> {
>>>>>>        public static void main( String arg[] )
>>>>>>                throws Exception
>>>>>>        {
>>>>>>                Class.forName( "org.postgresql.Driver" );
>>>>>>                Connection conn = DriverManager.getConnection(
>>>>>> "jdbc:postgresql://localhost/test", "postgres", "" );
>>>>>>                Statement create = conn.createStatement();
>>>>>>                create.execute( "CREATE TABLE t(id SERIAL, name
>>>>>> VARCHAR);" );
>>>>>>                create.close();
>>>>>>                long l = System.currentTimeMillis();
>>>>>>                for( int i = 0; i < 10000; ++i )
>>>>>>                {
>>>>>>                        Statement stm = conn.createStatement();
>>>>>>                        stm.execute( "SELECT id, name FROM t;" );
>>>>>>                        ResultSet rs = stm.getResultSet();
>>>>>>                        ResultSetMetaData rsmd = rs.getMetaData();
>>>>>>                        rsmd.getColumnType( 1 );
>>>>>> //                      rsmd.getColumnTypeName( 1 );
>>>>>>                        rs.close();
>>>>>>                        stm.close();
>>>>>>                }
>>>>>>                System.out.println( "getColumnType time (ms): " + (
>>>>>> System.currentTimeMillis() - l ) );
>>>>>>                l = System.currentTimeMillis();
>>>>>>                for( int i = 0; i < 10000; ++i )
>>>>>>                {
>>>>>>                        Statement stm = conn.createStatement();
>>>>>>                        stm.execute( "SELECT id, name FROM t;" );
>>>>>>                        ResultSet rs = stm.getResultSet();
>>>>>>                        ResultSetMetaData rsmd = rs.getMetaData();
>>>>>> //                      rsmd.getColumnType( 1 );
>>>>>>                        rsmd.getColumnTypeName( 1 );
>>>>>>                        rs.close();
>>>>>>                        stm.close();
>>>>>>                }
>>>>>>                System.out.println( "getColumnTypeName time (ms): " + (
>>>>>> System.currentTimeMillis() - l ) );
>>>>>>                l = System.currentTimeMillis();
>>>>>>                for( int i = 0; i < 10000; ++i )
>>>>>>                {
>>>>>>                        Statement stm = conn.createStatement();
>>>>>>                        stm.execute( "SELECT id, name FROM t;" );
>>>>>>                        ResultSet rs = stm.getResultSet();
>>>>>>                        ResultSetMetaData rsmd = rs.getMetaData();
>>>>>>                        rsmd.getColumnType( 1 );
>>>>>>                        rsmd.getColumnTypeName( 1 );
>>>>>>                        rs.close();
>>>>>>                        stm.close();
>>>>>>                }
>>>>>>                System.out.println( "both time (ms): " + (
>>>>>> System.currentTimeMillis() - l ) );
>>>>>>                Statement drop = conn.createStatement();
>>>>>>                drop.execute( "DROP TABLE t;" );
>>>>>>                drop.close();
>>>>>>                conn.close();
>>>>>>        }
>>>>>> }
>>>>>>
>>>>>>
>>>>>> Luis Flores
>>>>>>
>>>>>> On Tue, Oct 9, 2012 at 5:48 PM, dmp <danap@ttc-cmc.net> wrote:
>>>>>>> Eyal Wilde wrote:
>>>>>>>>
>>>>>>>> I verified with wireshark that  getColumnTypeName indeed do a request
>>>>>>>> to
>>>>>>>> the postgres server. This happens with 9.1-901 and 9.1-903 BUT NOT
>>>>>>>> WITH
>>>>>>>> 9.0-801!!
>>>>>>>>
>>>>>>>> So, the reason for the slowness seems to be obviouse now.
>>>>>>>>
>>>>>>>> it will take me some time to wrap up it into a test case. I would
>>>>>>>> realy
>>>>>>>> like to help, but please let me know if it's realy necessary.
>>>>>>>>
>>>>>>>> On Oct 5, 2012 1:07 AM, "Eyal Wilde" <eyal@impactsoft.co.il
>>>>>>>> <mailto:eyal@impactsoft.co.il>> wrote:
>>>>>>>>
>>>>>>>>
>>>>>>>>                         ResultSetMetaData __md = __rs.getMetaData();
>>>>>>>>      //this is fine
>>>>>>>>                         String __sf1name = __md.getColumnName(1);
>>>>>>>>       //this is fine
>>>>>>>>                         int __if1type = __md.getColumnType(1);
>>>>>>>>            //this is fine
>>>>>>>>                         String __sf1type =
>>>>>>>>    __md.getColumnTypeName(1); //this is SLOW!! ~15msec
>>>>>>>>
>>>>>>>>    postgres server version is 9.1.5
>>>>>>>>    jdbc version, i checked both 9.1-901 and 9.1-903
>>>>>>>>    and... BTW, 9.0-801 works good (while connected pg9.1)!
>>>>>>>
>>>>>>>
>>>>>>> There does appear to be a change in the code that may have created the
>>>>>>> slowness that you are observing. Please try a a test case in which two
>>>>>>> back to back getColumnTypeName() calls are made. Is there a difference
>>>>>>> in time between the first and second and is there still on the second
>>>>>>> call a request to the postgres server.
>>>>>>>
>>>>>>> The code between 9.0-801 and later version does have a change in it
>>>>>>> that looks like for the caching for field metadata through
>>>>>>> fetchFieldMetaData().
>>>>>>> That method is called in the later versions for getColumnTypeName()
>>>>>>> with isAutoIncrement(), with was also added in later versions.
>>>>>>>
>>>>>>> danap.
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
>>>>>>> To make changes to your subscription:
>>>>>>> http://www.postgresql.org/mailpref/pgsql-jdbc
>>
>>
>> --
>> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-jdbc

On Thu, Oct 11, 2012 at 7:07 AM, Luis Flores <luiscamposflores@gmail.com> wrote:
> If you use a column with a default value from a sequence, the column
> type name doesn't change. If the column is int AND is generated from a
> sequence called <TABLE>_<COLUMN>_seq, then in postgresql (you can
> check in pgadmin) the column type name name changes from integer to
> serial. We may or may not agree with this, but it's the way postgresql
> works, so, the jdbc driver correctness and naming should be in line
> with the postgresql server naming (mapping to jdbc type naming).

For what it's worth, based on pg_attribute, this seems to be a
pgAdmin-ism rather than the way Postgres works. The column type as
reported by the catalogs is "integer" whether you create a serial-type
column or an integer column with a default from your own sequence.

You're absolutely correct, I though the serial name was also reported
in psql, but no, only in pgadmin, so, like you said, in postgresql
there are only int4 and int8.

Which means we (or the driver maintainers, to be more precise) must
choose, postgresql complaint (int4) or jdbc complaint (serial)...

But changing this type of things is not nice for applications ...

Luis Flores


On Thu, Oct 11, 2012 at 4:45 PM, Maciek Sakrejda <m.sakrejda@gmail.com> wrote:
> On Thu, Oct 11, 2012 at 7:07 AM, Luis Flores <luiscamposflores@gmail.com> wrote:
>> If you use a column with a default value from a sequence, the column
>> type name doesn't change. If the column is int AND is generated from a
>> sequence called <TABLE>_<COLUMN>_seq, then in postgresql (you can
>> check in pgadmin) the column type name name changes from integer to
>> serial. We may or may not agree with this, but it's the way postgresql
>> works, so, the jdbc driver correctness and naming should be in line
>> with the postgresql server naming (mapping to jdbc type naming).
>
> For what it's worth, based on pg_attribute, this seems to be a
> pgAdmin-ism rather than the way Postgres works. The column type as
> reported by the catalogs is "integer" whether you create a serial-type
> column or an integer column with a default from your own sequence.

On 10/11/2012 10:07 PM, Luis Flores wrote:
> You are talking about a different matter.
> 1. 802 and prior drivers named serials as int4 (and big serials int8)
> 2. 900 and newer drivers named serials as serials (and you got a
> performance hit from that)
>
> We are now attempting to solve the problem #2, maintain the correct
> column name (serial and big serial), and if possible improve
> performance.

I strongly disagree with this approach. "serial" and "bigserial" are
*not* the correct column names. "serial" and "bigserial" are effectively
macros and cease to exist in any meaningful way once expanded.

Even if showing "serial" or "bigserial" were free, I'd still want to
show the underlying types "integer" and "biginteger".

Showing "serial" is not consistent with psql:

regress=# CREATE TABLE blah ( id serial primary key);
NOTICE:  CREATE TABLE will create implicit sequence "blah_id_seq" for
serial column "blah.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"blah_pkey" for table "blah"
CREATE TABLE
regress=# \d blah
                          Table "public.blah"
  Column |  Type   |                     Modifiers
--------+---------+---------------------------------------------------
  id     | integer | not null default nextval('blah_id_seq'::regclass)
Indexes:
     "blah_pkey" PRIMARY KEY, btree (id)


... nor with INFORMATION_SCHEMA:

select * from information_schema.columns WHERE table_schema = 'public'
AND table_name = 'blah';

-[ RECORD 1 ]------------+---------------------------------
table_catalog            | regress
table_schema             | public
table_name               | blah
column_name              | id
ordinal_position         | 1
column_default           | nextval('blah_id_seq'::regclass)
is_nullable              | NO
data_type                | integer
numeric_precision        | 32
numeric_precision_radix  | 2
numeric_scale            | 0
...
udt_catalog              | regress
udt_schema               | pg_catalog
udt_name                 | int4
...
dtd_identifier           | 1
is_self_referencing      | NO
is_identity              | NO
...
is_generated             | NEVER
...
is_updatable             | YES


> If you use a column with a default value from a sequence, the column
> type name doesn't change. If the column is int AND is generated from a
> sequence called <TABLE>_<COLUMN>_seq, then in postgresql (you can
> check in pgadmin)

PgAdmin is doing something funky; that doesn't mean PgJDBC should.

--
Craig Ringer

Craig Ringer, 12.10.2012 03:17:
>> You are talking about a different matter.
>> 1. 802 and prior drivers named serials as int4 (and big serials int8)
>> 2. 900 and newer drivers named serials as serials (and you got a
>> performance hit from that)
>>
>> We are now attempting to solve the problem #2, maintain the correct
>> column name (serial and big serial), and if possible improve
>> performance.
>
> I strongly disagree with this approach. "serial" and "bigserial" are *not* the correct column names. "serial" and
"bigserial"are effectively macros and cease to exist in any meaningful way once expanded. 
>
> Even if showing "serial" or "bigserial" were free, I'd still want to show the underlying types "integer" and
"biginteger".
>
> Showing "serial" is not consistent with psql:

But it is consistent with what an "end-user" would expect.

When looking at a CREATE TABLE statement in a SQL tool, users expect to see "serial" there if the table was created
thatway. 

Regards
Thomas

On 10/12/2012 02:33 PM, Thomas Kellerer wrote:

> When looking at a CREATE TABLE statement in a SQL tool, users expect to
> see "serial" there if the table was created that way.

Applications can check the metadata and make any transformations they
want for display. The fact remains that the data type is "integer" or
"biginteger".

JDBC isn't for the "end user", it's an API for developers, much like libpq.

I maintain that presenting "serial" or "bigserial" as a *type* is just
plain wrong. If nothing else:

regress=# create table blah ( id serial primary key );
CREATE TABLE
regress=# PREPARE insert_blah(serial) AS INSERT INTO blah(id)
regress-# VALUES ($1);
ERROR:  type "serial" does not exist
LINE 1: PREPARE insert_blah(serial) AS INSERT INTO blah(id) VALUES (...

Also:

regress=# SELECT '1'::serial;
ERROR:  type "serial" does not exist
LINE 1: SELECT '1'::serial;

Seriously, reporting the type as "serial" is just plain wrong.

--
Craig Ringer

I agree, my only doubt is about the reasons behind the change, the driver was reporting int4, int8, and then was
changed,why? 

Luis Flores

On 12/10/2012, at 08:04, Craig Ringer <ringerc@ringerc.id.au> wrote:

> On 10/12/2012 02:33 PM, Thomas Kellerer wrote:
>
>> When looking at a CREATE TABLE statement in a SQL tool, users expect to
>> see "serial" there if the table was created that way.
>
> Applications can check the metadata and make any transformations they want for display. The fact remains that the
datatype is "integer" or "biginteger". 
>
> JDBC isn't for the "end user", it's an API for developers, much like libpq.
>
> I maintain that presenting "serial" or "bigserial" as a *type* is just plain wrong. If nothing else:
>
> regress=# create table blah ( id serial primary key );
> CREATE TABLE
> regress=# PREPARE insert_blah(serial) AS INSERT INTO blah(id)
> regress-# VALUES ($1);
> ERROR:  type "serial" does not exist
> LINE 1: PREPARE insert_blah(serial) AS INSERT INTO blah(id) VALUES (...
>
> Also:
>
> regress=# SELECT '1'::serial;
> ERROR:  type "serial" does not exist
> LINE 1: SELECT '1'::serial;
>
> Seriously, reporting the type as "serial" is just plain wrong.
>
> --
> Craig Ringer
>
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc

I would have to agree with Craig's assessment here. serial and
bigserial are pseudotypes.

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


On Fri, Oct 12, 2012 at 3:04 AM, Craig Ringer <ringerc@ringerc.id.au> wrote:
> On 10/12/2012 02:33 PM, Thomas Kellerer wrote:
>
>> When looking at a CREATE TABLE statement in a SQL tool, users expect to
>> see "serial" there if the table was created that way.
>
>
> Applications can check the metadata and make any transformations they want
> for display. The fact remains that the data type is "integer" or
> "biginteger".
>
> JDBC isn't for the "end user", it's an API for developers, much like libpq.
>
> I maintain that presenting "serial" or "bigserial" as a *type* is just plain
> wrong. If nothing else:
>
> regress=# create table blah ( id serial primary key );
> CREATE TABLE
> regress=# PREPARE insert_blah(serial) AS INSERT INTO blah(id)
> regress-# VALUES ($1);
> ERROR:  type "serial" does not exist
> LINE 1: PREPARE insert_blah(serial) AS INSERT INTO blah(id) VALUES (...
>
> Also:
>
> regress=# SELECT '1'::serial;
> ERROR:  type "serial" does not exist
> LINE 1: SELECT '1'::serial;
>
> Seriously, reporting the type as "serial" is just plain wrong.
>
> --
> Craig Ringer
>
>
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc

On 10/12/2012 04:39 PM, Luis Flores wrote:
> I agree, my only doubt is about the reasons behind the change, the driver was reporting int4, int8, and then was
changed,why? 

Yep, that I'd like to know. The change appears to be:

commit ddf8296bead68552a8e5de0f5bb20875273bb02d
Author: Kris Jurka <books@ejurka.com>
Date:   Thu Sep 30 07:58:11 2004 +0000

     Return serial datatypes in both getTypeInfo and getColumns methods.

     Jaroslaw J. Pysnzy

     Also add a test case for this and fix my previous regression test
     breakage on 7.2 servers.  With serial columns before dependency
     information dropping a table did not drop the sequences that went
     with it.  Explicitly drop them.

     Kris Jurka



see git diff
ddf8296bead68552a8e5de0f5bb20875273bb02d..8c9d68ee7763851732de0bd0d14b2b51cdfe0622


That code is all kinds of wrong. Check this out:

if ( defval != null ) {
      if ( pgType.equals("int4") ) {
          if (defval.indexOf("nextval(") != -1)
               tuple[5] = connection.encodeString("serial");
      }
      else if ( pgType.equals("int8") ) {
          if (defval.indexOf("nextval(") != -1)
               tuple[5] = connection.encodeString("bigserial");
      }
}



*any* int4 or int8 with a DEFAULT nextval(... is reported as "serial" or
"bigserial" whether or not it is. See AbstractJdbc2DatabaseMetaData.java
line 2480.

--
Craig Ringer

So what is the consensus on this.

I would lean towards removing serial types here

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


On Fri, Oct 12, 2012 at 6:06 AM, Craig Ringer <ringerc@ringerc.id.au> wrote:
> On 10/12/2012 04:39 PM, Luis Flores wrote:
>>
>> I agree, my only doubt is about the reasons behind the change, the driver
>> was reporting int4, int8, and then was changed, why?
>
>
> Yep, that I'd like to know. The change appears to be:
>
> commit ddf8296bead68552a8e5de0f5bb20875273bb02d
> Author: Kris Jurka <books@ejurka.com>
> Date:   Thu Sep 30 07:58:11 2004 +0000
>
>     Return serial datatypes in both getTypeInfo and getColumns methods.
>
>     Jaroslaw J. Pysnzy
>
>     Also add a test case for this and fix my previous regression test
>     breakage on 7.2 servers.  With serial columns before dependency
>     information dropping a table did not drop the sequences that went
>     with it.  Explicitly drop them.
>
>     Kris Jurka
>
>
>
> see git diff
> ddf8296bead68552a8e5de0f5bb20875273bb02d..8c9d68ee7763851732de0bd0d14b2b51cdfe0622
>
>
> That code is all kinds of wrong. Check this out:
>
> if ( defval != null ) {
>      if ( pgType.equals("int4") ) {
>          if (defval.indexOf("nextval(") != -1)
>               tuple[5] = connection.encodeString("serial");
>      }
>      else if ( pgType.equals("int8") ) {
>          if (defval.indexOf("nextval(") != -1)
>               tuple[5] = connection.encodeString("bigserial");
>      }
> }
>
>
>
> *any* int4 or int8 with a DEFAULT nextval(... is reported as "serial" or
> "bigserial" whether or not it is. See AbstractJdbc2DatabaseMetaData.java
> line 2480.
>
>
> --
> Craig Ringer
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc

On 10/17/2012 07:28 AM, Dave Cramer wrote:
> So what is the consensus on this.
>
> I would lean towards removing serial types here

I certainly want to, as I think the status quo is not only inefficient
and incorrect but it fails to even function as designed (incorrectly
calling 'integer' columns generated by a sequence 'serial' no matter
what the sequence is) and cannot easily be fixed.

The questions that must still be answered are:

- How do we tell developers about the change?

- Is a compatibility flag required?

- What might it break? Do we need to test popular ORM systems or let
   their developers know?

- What does the metadata spec actually require us to supply?


The 9.2 driver has been pushed and it's a backwards-incompatible change,
so it's probably a change that's only suitable for 9.3/master. I'm
inclined to just make the change in master, push a beta driver to the
website, and see it reports come in. I can test Hibernate and
EclipseLink out, but I'm pretty sure they won't care.


BTW, I'm quite surprised that PostgreSQL doesn't expose information
about column value generation in INFORMATION_SCHEMA.COLUMNS at the
moment; the "is_identity" column is always "NO" and "is_generated" is
always "NEVER". `pg_attribute` doesn't seem to convey it either.
Shouldn't we be able to ask PostgreSQL for this information?

I'll have a look and see how the driver currently reports whether fields
are generated, are identity fields, etc.

Looking at the number of issue reports that focus on metadata, I suspect
that's a real weak point of the current driver and really needs a
review. Yay. Something for all that abundant spare time.

--
Craig Ringer

On 17/10/12 17:56, Craig Ringer wrote:
> On 10/17/2012 07:28 AM, Dave Cramer wrote:
>> So what is the consensus on this.
>>
>> I would lean towards removing serial types here
>
> I certainly want to, as I think the status quo is not only inefficient
> and incorrect but it fails to even function as designed (incorrectly
> calling 'integer' columns generated by a sequence 'serial' no matter
> what the sequence is) and cannot easily be fixed.
>
> The questions that must still be answered are:
>
> - How do we tell developers about the change?
>
> - Is a compatibility flag required?
>
> - What might it break? Do we need to test popular ORM systems or let
>   their developers know?
>
> - What does the metadata spec actually require us to supply?
>
>
> The 9.2 driver has been pushed and it's a backwards-incompatible
> change, so it's probably a change that's only suitable for 9.3/master.
> I'm inclined to just make the change in master, push a beta driver to
> the website, and see it reports come in. I can test Hibernate and
> EclipseLink out, but I'm pretty sure they won't care.
>
>
> BTW, I'm quite surprised that PostgreSQL doesn't expose information
> about column value generation in INFORMATION_SCHEMA.COLUMNS at the
> moment; the "is_identity" column is always "NO" and "is_generated" is
> always "NEVER". `pg_attribute` doesn't seem to convey it either.
> Shouldn't we be able to ask PostgreSQL for this information?
>
> I'll have a look and see how the driver currently reports whether
> fields are generated, are identity fields, etc.
>
> Looking at the number of issue reports that focus on metadata, I
> suspect that's a real weak point of the current driver and really
> needs a review. Yay. Something for all that abundant spare time.
>
> --
> Craig Ringer
>
>
Come on!

You were getting bored out of your tiny little mind!!

<Quickly ducks, and runs away _VERY_ FAST_!!!>


Cheers,
Gavin

On Wed, Oct 17, 2012 at 12:56 AM, Craig Ringer <ringerc@ringerc.id.au> wrote:
> On 10/17/2012 07:28 AM, Dave Cramer wrote:
>>
>> So what is the consensus on this.
>>
>> I would lean towards removing serial types here
>
>
> I certainly want to, as I think the status quo is not only inefficient and
> incorrect but it fails to even function as designed (incorrectly calling
> 'integer' columns generated by a sequence 'serial' no matter what the
> sequence is) and cannot easily be fixed.
>
> The questions that must still be answered are:
>
> - How do we tell developers about the change?
>
> - Is a compatibility flag required?
>
> - What might it break? Do we need to test popular ORM systems or let
>   their developers know?
>
> - What does the metadata spec actually require us to supply?
>
>
> The 9.2 driver has been pushed and it's a backwards-incompatible change, so
> it's probably a change that's only suitable for 9.3/master. I'm inclined to
> just make the change in master, push a beta driver to the website, and see
> it reports come in. I can test Hibernate and EclipseLink out, but I'm pretty
> sure they won't care.
>
>
> BTW, I'm quite surprised that PostgreSQL doesn't expose information about
> column value generation in INFORMATION_SCHEMA.COLUMNS at the moment; the
> "is_identity" column is always "NO" and "is_generated" is always "NEVER".
> `pg_attribute` doesn't seem to convey it either. Shouldn't we be able to ask
> PostgreSQL for this information?
>
> I'll have a look and see how the driver currently reports whether fields are
> generated, are identity fields, etc.
>
> Looking at the number of issue reports that focus on metadata, I suspect
> that's a real weak point of the current driver and really needs a review.
> Yay. Something for all that abundant spare time.

So I spent some more time on this. This change was made in 2004. Why
is it only a problem now ?

Yes, metadata is the big issue. Postgres just doesn't expose it like
others do. If it were easy the metadata would have been done.

As far as int + default nextval goes, that sort of is the definition
of a serial.

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


>
>
> --
> Craig Ringer
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc

The way postgresql implements serial (int from a sequence, with a
alias of serial) is really nice because it gives sequence and serial
interface to a column, but looks like a hack.

We create a column with type serial, when we ask postgresql to
describe it, it says it's an int.

The JDBC driver (or any other driver) should not fix/circumvent
postgresql definition, but only to map the database types to JDBC
types.

Is the database defines the type as an int, then it's an int alright.
The JDBC driver is just a bridge, a connector, it should not define
new data types.

In the current implementation, the result of getColumnType and
getColumnTypeName is also incoherent, int constant vs "serial" name.

So, work should be done upstream to fix this, at the database layer,
they should make up their mind, is it an int or a serial, serial is a
real type, or just an alias?

For now, it seams to me, the more correct thing is to deliver what the
database delivers ... int


Luis Flores

On Wed, Oct 17, 2012 at 10:27 AM, Dave Cramer <pg@fastcrypt.com> wrote:
> On Wed, Oct 17, 2012 at 12:56 AM, Craig Ringer <ringerc@ringerc.id.au> wrote:
>> On 10/17/2012 07:28 AM, Dave Cramer wrote:
>>>
>>> So what is the consensus on this.
>>>
>>> I would lean towards removing serial types here
>>
>>
>> I certainly want to, as I think the status quo is not only inefficient and
>> incorrect but it fails to even function as designed (incorrectly calling
>> 'integer' columns generated by a sequence 'serial' no matter what the
>> sequence is) and cannot easily be fixed.
>>
>> The questions that must still be answered are:
>>
>> - How do we tell developers about the change?
>>
>> - Is a compatibility flag required?
>>
>> - What might it break? Do we need to test popular ORM systems or let
>>   their developers know?
>>
>> - What does the metadata spec actually require us to supply?
>>
>>
>> The 9.2 driver has been pushed and it's a backwards-incompatible change, so
>> it's probably a change that's only suitable for 9.3/master. I'm inclined to
>> just make the change in master, push a beta driver to the website, and see
>> it reports come in. I can test Hibernate and EclipseLink out, but I'm pretty
>> sure they won't care.
>>
>>
>> BTW, I'm quite surprised that PostgreSQL doesn't expose information about
>> column value generation in INFORMATION_SCHEMA.COLUMNS at the moment; the
>> "is_identity" column is always "NO" and "is_generated" is always "NEVER".
>> `pg_attribute` doesn't seem to convey it either. Shouldn't we be able to ask
>> PostgreSQL for this information?
>>
>> I'll have a look and see how the driver currently reports whether fields are
>> generated, are identity fields, etc.
>>
>> Looking at the number of issue reports that focus on metadata, I suspect
>> that's a real weak point of the current driver and really needs a review.
>> Yay. Something for all that abundant spare time.
>
> So I spent some more time on this. This change was made in 2004. Why
> is it only a problem now ?
>
> Yes, metadata is the big issue. Postgres just doesn't expose it like
> others do. If it were easy the metadata would have been done.
>
> As far as int + default nextval goes, that sort of is the definition
> of a serial.
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
>
>>
>>
>> --
>> Craig Ringer
>>
>>
>> --
>> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-jdbc

On Wed, Oct 17, 2012 at 6:43 AM, Luis Flores <luiscamposflores@gmail.com> wrote:
> The way postgresql implements serial (int from a sequence, with a
> alias of serial) is really nice because it gives sequence and serial
> interface to a column, but looks like a hack.
>
> We create a column with type serial, when we ask postgresql to
> describe it, it says it's an int.
>
> The JDBC driver (or any other driver) should not fix/circumvent
> postgresql definition, but only to map the database types to JDBC
> types.
>
> Is the database defines the type as an int, then it's an int alright.
> The JDBC driver is just a bridge, a connector, it should not define
> new data types.
>
> In the current implementation, the result of getColumnType and
> getColumnTypeName is also incoherent, int constant vs "serial" name.
>
> So, work should be done upstream to fix this, at the database layer,
> they should make up their mind, is it an int or a serial, serial is a
> real type, or just an alias?
>
> For now, it seams to me, the more correct thing is to deliver what the
> database delivers ... int
>
>

Luis,

We're sort of all in agreement with above, however removing the
existing code may break existing code.


Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca



> Luis Flores
>
> On Wed, Oct 17, 2012 at 10:27 AM, Dave Cramer <pg@fastcrypt.com> wrote:
>> On Wed, Oct 17, 2012 at 12:56 AM, Craig Ringer <ringerc@ringerc.id.au> wrote:
>>> On 10/17/2012 07:28 AM, Dave Cramer wrote:
>>>>
>>>> So what is the consensus on this.
>>>>
>>>> I would lean towards removing serial types here
>>>
>>>
>>> I certainly want to, as I think the status quo is not only inefficient and
>>> incorrect but it fails to even function as designed (incorrectly calling
>>> 'integer' columns generated by a sequence 'serial' no matter what the
>>> sequence is) and cannot easily be fixed.
>>>
>>> The questions that must still be answered are:
>>>
>>> - How do we tell developers about the change?
>>>
>>> - Is a compatibility flag required?
>>>
>>> - What might it break? Do we need to test popular ORM systems or let
>>>   their developers know?
>>>
>>> - What does the metadata spec actually require us to supply?
>>>
>>>
>>> The 9.2 driver has been pushed and it's a backwards-incompatible change, so
>>> it's probably a change that's only suitable for 9.3/master. I'm inclined to
>>> just make the change in master, push a beta driver to the website, and see
>>> it reports come in. I can test Hibernate and EclipseLink out, but I'm pretty
>>> sure they won't care.
>>>
>>>
>>> BTW, I'm quite surprised that PostgreSQL doesn't expose information about
>>> column value generation in INFORMATION_SCHEMA.COLUMNS at the moment; the
>>> "is_identity" column is always "NO" and "is_generated" is always "NEVER".
>>> `pg_attribute` doesn't seem to convey it either. Shouldn't we be able to ask
>>> PostgreSQL for this information?
>>>
>>> I'll have a look and see how the driver currently reports whether fields are
>>> generated, are identity fields, etc.
>>>
>>> Looking at the number of issue reports that focus on metadata, I suspect
>>> that's a real weak point of the current driver and really needs a review.
>>> Yay. Something for all that abundant spare time.
>>
>> So I spent some more time on this. This change was made in 2004. Why
>> is it only a problem now ?
>>
>> Yes, metadata is the big issue. Postgres just doesn't expose it like
>> others do. If it were easy the metadata would have been done.
>>
>> As far as int + default nextval goes, that sort of is the definition
>> of a serial.
>>
>> Dave Cramer
>>
>> dave.cramer(at)credativ(dot)ca
>> http://www.credativ.ca
>>
>>
>>>
>>>
>>> --
>>> Craig Ringer
>>>
>>>
>>> --
>>> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-jdbc
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc

Like Craig said, schedule the change to 9.3, in the meanwhile, apply
my patch, just to improve cases where no serials are involved.

Luis Flores

On Wed, Oct 17, 2012 at 12:13 PM, Dave Cramer <pg@fastcrypt.com> wrote:
> On Wed, Oct 17, 2012 at 6:43 AM, Luis Flores <luiscamposflores@gmail.com> wrote:
>> The way postgresql implements serial (int from a sequence, with a
>> alias of serial) is really nice because it gives sequence and serial
>> interface to a column, but looks like a hack.
>>
>> We create a column with type serial, when we ask postgresql to
>> describe it, it says it's an int.
>>
>> The JDBC driver (or any other driver) should not fix/circumvent
>> postgresql definition, but only to map the database types to JDBC
>> types.
>>
>> Is the database defines the type as an int, then it's an int alright.
>> The JDBC driver is just a bridge, a connector, it should not define
>> new data types.
>>
>> In the current implementation, the result of getColumnType and
>> getColumnTypeName is also incoherent, int constant vs "serial" name.
>>
>> So, work should be done upstream to fix this, at the database layer,
>> they should make up their mind, is it an int or a serial, serial is a
>> real type, or just an alias?
>>
>> For now, it seams to me, the more correct thing is to deliver what the
>> database delivers ... int
>>
>>
>
> Luis,
>
> We're sort of all in agreement with above, however removing the
> existing code may break existing code.
>
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
>
>
>> Luis Flores
>>
>> On Wed, Oct 17, 2012 at 10:27 AM, Dave Cramer <pg@fastcrypt.com> wrote:
>>> On Wed, Oct 17, 2012 at 12:56 AM, Craig Ringer <ringerc@ringerc.id.au> wrote:
>>>> On 10/17/2012 07:28 AM, Dave Cramer wrote:
>>>>>
>>>>> So what is the consensus on this.
>>>>>
>>>>> I would lean towards removing serial types here
>>>>
>>>>
>>>> I certainly want to, as I think the status quo is not only inefficient and
>>>> incorrect but it fails to even function as designed (incorrectly calling
>>>> 'integer' columns generated by a sequence 'serial' no matter what the
>>>> sequence is) and cannot easily be fixed.
>>>>
>>>> The questions that must still be answered are:
>>>>
>>>> - How do we tell developers about the change?
>>>>
>>>> - Is a compatibility flag required?
>>>>
>>>> - What might it break? Do we need to test popular ORM systems or let
>>>>   their developers know?
>>>>
>>>> - What does the metadata spec actually require us to supply?
>>>>
>>>>
>>>> The 9.2 driver has been pushed and it's a backwards-incompatible change, so
>>>> it's probably a change that's only suitable for 9.3/master. I'm inclined to
>>>> just make the change in master, push a beta driver to the website, and see
>>>> it reports come in. I can test Hibernate and EclipseLink out, but I'm pretty
>>>> sure they won't care.
>>>>
>>>>
>>>> BTW, I'm quite surprised that PostgreSQL doesn't expose information about
>>>> column value generation in INFORMATION_SCHEMA.COLUMNS at the moment; the
>>>> "is_identity" column is always "NO" and "is_generated" is always "NEVER".
>>>> `pg_attribute` doesn't seem to convey it either. Shouldn't we be able to ask
>>>> PostgreSQL for this information?
>>>>
>>>> I'll have a look and see how the driver currently reports whether fields are
>>>> generated, are identity fields, etc.
>>>>
>>>> Looking at the number of issue reports that focus on metadata, I suspect
>>>> that's a real weak point of the current driver and really needs a review.
>>>> Yay. Something for all that abundant spare time.
>>>
>>> So I spent some more time on this. This change was made in 2004. Why
>>> is it only a problem now ?
>>>
>>> Yes, metadata is the big issue. Postgres just doesn't expose it like
>>> others do. If it were easy the metadata would have been done.
>>>
>>> As far as int + default nextval goes, that sort of is the definition
>>> of a serial.
>>>
>>> Dave Cramer
>>>
>>> dave.cramer(at)credativ(dot)ca
>>> http://www.credativ.ca
>>>
>>>
>>>>
>>>>
>>>> --
>>>> Craig Ringer
>>>>
>>>>
>>>> --
>>>> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
>>>> To make changes to your subscription:
>>>> http://www.postgresql.org/mailpref/pgsql-jdbc
>>
>>
>> --
>> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-jdbc