s9y_200502151337: Get error with Postgres on new entry

[LER]

I'm a noobie, but like to help find bugs

Whenever I add a new entry, I get the following:

Code: Select all

Warning: pg_query(): Query failed: ERROR: syntax error at end of input at character 46 in /home/ler/serendipity/include/db/postgres.inc.php on line 83

Warning: pg_num_rows(): supplied argument is not a valid PostgreSQL result resource in /home/ler/serendipity/include/db/postgres.inc.php on line 84

Warning: pg_query(): Query failed: ERROR: syntax error at or near "," at character 64 in /home/ler/serendipity/include/db/postgres.inc.php on line 102
Error in INSERT INTO serendipity_entrycat (entryid, categoryid) VALUES (, 1)
ERROR: syntax error at or near "," at character 64
array (
  0 =>
  array (
    'file' => '/home/ler/serendipity/include/functions_entries.inc.php',
    'line' => 953,
    'function' => 'serendipity_db_query',
    'args' =>
    array (
      0 => 'INSERT INTO serendipity_entrycat (entryid, categoryid) VALUES (, 1)',
    ),
  ),
  1 =>
  array (
    'file' => '/home/ler/serendipity/include/functions_config.inc.php',
    'line' => 329,
    'function' => 'serendipity_updertentry',
    'args' =>
    array (
      0 =>
      array (
        'id' => '',
        'title' => 'Test Entry for DEV\'s...',
        'timestamp' => '1108513789',
        'body' => 'This is a test entry so I can get the traceback :)

LER
',
        'extended' => '',
        'categories' =>
        array (
          0 => '1',
        ),
        'isdraft' => 'false',
        'allow_comments' => 'true',
        'moderate_comments' => 'false',
        'exflag' => false,
      ),
    ),
  ),
  2 =>
  array (
    'file' => '/home/ler/serendipity/include/functions_config.inc.php',
    'line' => 308,
    'function' => 'serendipity_iframe',
    'args' =>
    array (
      0 =>
      array (
        'id' => '',
        'title' => 'Test Entry for DEV\'s...',
        'timestamp' => '1108513789',
        'body' => 'This is a test entry so I can get the traceback :)

LER
',
        'extended' => '',
        'categories' =>
        array (
          0 => '1',
        ),
        'isdraft' => 'false',
        'allow_comments' => 'true',
        'moderate_comments' => 'false',
        'exflag' => false,
      ),
      1 => 'save',
    ),
  ),
  3 =>
  array (
    'file' => '/home/ler/serendipity/serendipity_admin.php',
    'line' => 28,
    'function' => 'serendipity_is_iframe',
    'args' =>
    array (
    ),
  ),
)
INSERT INTO serendipity_entrycat (entryid, categoryid) VALUES (, 1)
Your entry has been saved

This is with PostgreSQL 8.0.1, and PHP 4.3.11-DEV.

How can I help?

[garvinhicking]

This looks like an error because of missing propagated session variables - the entry ID is missing so that Serendipity can't insert the entry relation. That means the initial creation of the entry fails and doesn't return an entryid.

Could you please edit your file include/db/postgres.inc.php and add those lines to the function_serendipity_db_query above the first if-statement? I include some lines before and after so that you know where to insert:

Code: Select all

function &serendipity_db_query($sql, $single = false, $result_type = "both", $reportErr = false, $assocKey = false, $assocVal = false, $expectError = false) {
    global $serendipity;
    static $type_map = array(
                         'assoc' => PGSQL_ASSOC,
                         'num'   => PGSQL_NUM,
                         'both'  => PGSQL_BOTH
    );

    /* INSERT THIS: */
    $fp = fopen('pgsql.log', 'a');
    fwrite($fp, '[' . date('d.m.Y H:i') . '] - [' . $_SERVER['REQUEST_URI'] . ']' . "\n");
    fwrite($fp, $sql . "\n\n");
    fclose($fp);
    /* END OF INSERT */

    if (!$expectError && ($reportErr || !$serendipity['production'])) {
        $serendipity['dbLastResult'] = pg_query($serendipity['dbConn'], $sql);
    } else {
        $serendipity['dbLastResult'] = @pg_query($serendipity['dbConn'], $sql);
    }

This instructs serendipity to trace every SQL statement. Now please try to save your entry again and then look in your serendipity directory for a file called 'pgsql.log' and please upload it somewhere so that I can look at it to treconstruct the queries sent to the server.

Many thanks for your help,
Garvin.

[LER]

the log is at:

http://www.lerctr.org/~ler/pgsql.log

this is with PHP 4.3.11-DEV and PGSQL 8.0.1

[garvinhicking]

Thanks for the fast reply!

This query:

Code: Select all

INSERT INTO serendipity_entries (title,timestamp,body,extended,isdraft,allow_comments,moderate_comments,exflag,last_modified,comments,author,authorid) values ('test entry', '1108551257', 'to get log for dev', '', 'false', 'true', 'false', '0', '1108551286', '0', 'LER', '1')

seems to fail. Do you have a tool like phpPgAdmin or similar and can try to execute the query manually, and see why/if it fails? Or see if the entry is inserted properly?

Regards,
Garvin

[LER]

Works fine:

Code: Select all

$ psql serendipity 
Welcome to psql 8.0.1, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

serendipity=# INSERT INTO serendipity_entries (title,timestamp,body,extended,is
draft,allow_comments,moderate_comments,exflag,last_modified,comments,author,aut
horid) values ('test entry', '1108551257', 'to get log for dev', '', 'false', '
true', 'false', '0', '1108551286', '0', 'LER', '1')
serendipity-# ;
INSERT 0 1
serendipity=# 

[LER]

One thing I just thought of. I have "default_with_oids" set to FALSE, which means you
don't get an OID for every row.

Since the PostgreSQL folks are trying to move away from OID's on every row, this is a good thing.

Do you depend on OID's for each row of your tables?

If so, you may need to add WITH OIDS to the create table.

I can also supply a Shell account for this box if you'd like, as well as access to whatever you need.

LER

[garvinhicking]

I don't know much about postgreSQL.

But yes, we need OIDs for tables. We need to get the last inserted ID with some way. MySQL does this via a PHP mysql_insert_id() function, SQLite does it via PHP sqlite_last_insert_rowid() function.

Surely PostgreSQL supports something similar and I only don't know about it? In your case you will only be able to fix it to enable oids...

About the "WITH OIDS" statement: Can you point me to a documentation about the full syntax of it, so that I can implement it into Serendipity's db.sql?

Regards,
Garvin

[LER]

But yes, we need OIDs for tables. We need to get the last inserted ID with some way. MySQL does this via a PHP mysql_insert_id() function, SQLite does it via PHP sqlite_last_insert_rowid() function.

Surely PostgreSQL supports something similar and I only don't know about it? In your case you will only be able to fix it to enable oids...

Yes, pg_last_oid. Why do you depend on OID's and not a sequence or other
user created value?

About the "WITH OIDS" statement: Can you point me to a documentation about the full syntax of it, so that I can implement it into Serendipity's db.sql?

see: http://www.lerctr.org/postgresql/sql-createtable.html
(which is from my copy of the PGSQL docs).

[LER]

Ok, I recreated your database adding WITH OIDS to each CREATE TABLE, and it does, in fact, fix the issue.

I'm firmly of the belief that DEPENDING on OIDS is a mistake, and should be looked into.

(OID's are a "scarce" resource).

How can I assist more?

[LER]

You could also add:

Code: Select all

SET default_with_oids = true;

to the PostgreSQL db.sql (for at least >= 8.0, but maybe 7.4 as well).

[garvinhicking]

The only thing to let us not depend on OIDs is a way to get the last insert id form a 'INSERT INTO' statement. If OIDs are deprectated by postgresql, what do they prefer instead to get the last insert id?

I'll add that 'set oid' statement to our SQL, thanks!

Regards,
Garvin

[LER]

The only thing to let us not depend on OIDs is a way to get the last insert id form a 'INSERT INTO' statement. If OIDs are deprectated by postgresql, what do they prefer instead to get the last insert id?

I'll add that 'set oid' statement to our SQL, thanks!

Regards,
Garvin

If the table doesn't have oid's, you can't get one

Why do you need the OID, and not a sequence or other reference between the tables?

[garvinhicking]

A sequence table would create additional tables and additional space that is not needed. The OID is needed to propagate the new entry id to Serendipity Forms and Session variables and to return it via XML-RPC if asked for.

And of course it works in all other SQL systems like that

Regards,
Garvin

[LER]

A sequence table would create additional tables and additional space that is not needed. The OID is needed to propagate the new entry id to Serendipity Forms and Session variables and to return it via XML-RPC if asked for.

And of course it works in all other SQL systems like that

Regards,
Garvin

Do you need it on ALL tables or just certain ones?

If you can get away with just having it on one or two tables, add WITH OIDS to the
SQL CREATE TABLE command.

I'm not going to get into an argument on it, but OID wraparound is the reason they've deprecated having OIDS on ALL rows of ALL tables.

If you have a real need, add WITH OIDS to the appropriate CREATE TABLE commands
would be better than having them on all.

my $0.02.

[garvinhicking]

The problem is that we do not have seperate postgresql .sql include files. Thus we cannot easily add the 'WITH' statement. And I won't dare to wade through the code to see were we need a OID.

I just want to rely we can always get the last insert ID. I don't this this is expected too much from a nice SQL system, the postgresql guys need to find a solution to this, IMHO.

Maybe you can bring up the issue on our mailinglist? Some users using postgresql are listening there and may add in their opinion, because as you can see, I'm more the mysql+sqlite guy

Regards,
Garvin