By jordi on

Once I've found I can continue using Drupal with PostgreSQL, here comes my next problem.

Apparently, my database has been getting more and more damaged on the trip from Drupal 4.1 (with alpha psql support) to 4.5. Right now, the site works at first glance, but quite a few things don't:

- it seems all the sequences were reset, or are not working properly. Doing stuff like creating a new vocabulary won't work.
- node hit count stopped working after the 4.5 upgrade
- psql permissions in the database might be wrong, and could be the root of all of this b0rkage.
- in general, I haven't completed an upgrade successfully since 4.2, I think I always need to cleanup due to the database inconsistency.

So, what can I do to fixup the database?

First I'd need to identify what parts of it are not synced with the current clean template.
I'll also have to go over all the sequence tables and check that they are correct. Can anyone tell me how to tell if the sequences are synchronised?
Finally, how should the database permissions look like? Right now, it's a mess, some tables look like this:
public | accesslog | {postgres=a*r*w*d*R*x*t*/postgres,triatlo=arwdRxt/postgres}
Others, like this:
public | comments_seq | {triatlo=a*r*w*d*R*x*t*/triatlo}
Others, which definitely look wrong (my psql user is in there):
public | watchdog | {jordi=a*r*w*d*R*x*t*/jordi,triatlo=arwdRxt/jordi}

The database runs on PostgreSQL 7.4.7, and the database user is "triatlo". How should these permissions look like?

Thanks in advance, lets see if I can get the database sorted out before attempting a 4.6 upgrade.

Comments

mhutch’s picture

mhutch commented

Hmm. My first install was 4.4, but for 4.5 to upgrade wouldn't work, so I did a clean install then ported all the settings and data across to the new DB. It's a lot of work, but if you export your entire DB as series of INSERT statements that makes it easier. You also have to fix up all the sequences... Maybe not such a good idea after all! My site *was* very small.

It's not too hard to fix the sequences with a graphical tool like pgAdmin III, just very time-consuming; you just sort the appropriate table by the appropriate ID to find the maximum value, and change the START property of the sequence to one increment up from that. That's how I did it last time!

I've just done a bit of research and found a quicker way to fix the sequences. Wish I'd done this last time!
The simplest way to fix sequence eg. 'table_id_seq' in SQL seems to be
SELECT max(id)+1 FROM table;
Then use the result as 'value' in
SELECT setval('table_id_seq', value);
You can combine these into
SELECT setval('table_id_seq', (SELECT max(id)+1 FROM table));

For example fixing the node hit counts: accesslog_aid_seq. I've just experimented and you can definately fix this (I broke it!) with
SELECT setval('accesslog_aid_seq', (SELECT max(aid)+1 FROM accesslog));

If you look at the specific errors you're getting in the watchdog, that should indicate which ones are out of sync. However, it wouldn't be too hard to construct a script with statements for all of the sequences. If you do, if yould could post the script to the troubleshooting section of the handbook on drupal.org that would be great for other users. This would also be useful for anyone porting data across from an old DB rather than upgrading.

I'm afraid I'm not the administrator on my PostgresSQL, but my DB account - that Drupal uses - is the owner of the DB, and no-one else has any privileges at all.

jordi’s picture

jordi commented

It seems one of the areas where the database is more badly broken is in locales. I have a correct Catalan locale installed, but trying to add a Spanish one spits a lot of errors, but the locale is added. Trying to populate it with a po file, though, is different:

warning: pg_query(): Query failed: ERROR:  duplicate key violates unique constraint "locales_target_lid_key" in /home/jordi/triatlo/includes/database.pgsql.inc on line 104.

user error:  query: INSERT INTO locales_target (lid, locale, translation) VALUES (3982, 'es', '%a comentarios por página') in /home/jordi/triatlo/includes/database.pgsql.inc on line 121.

Damn, this is not looking too good.

cesar7073’s picture

cesar7073 commented

Hi, i've created a view and a function to restore all sequences' current values to the max id + 1 of the table's rows.
Restrictions are:
- Primary key must be the first attribute of the table (attnum = 1).
- All sequences must start with the name of the table and end with _seq.

CREATE OR REPLACE VIEW pga_pcolumns (
    tablename,
    columnname,
    sequencename)
AS
SELECT pg_class.relname AS tablename, pg_attribute.attname AS columnname, (
    SELECT seq_class.relname
    FROM pg_class seq_class
    WHERE (seq_class.relname ~~ ((pg_class.relname)::text || '_%_seq'::text))
    LIMIT 1
    ) AS sequencename
FROM pg_class, pg_attribute
WHERE (((pg_class.oid = pg_attribute.attrelid) AND (pg_attribute.attnum = 1))
    AND (pg_class.relkind = 'r'::"char"))
ORDER BY pg_class.relname, pg_attribute.attnum;
	
CREATE OR REPLACE FUNCTION "public"."fixContraints" () RETURNS integer AS
$body$
declare
       table_obj record;
begin
     for table_obj in execute 'select * from pga_pcolumns' loop
         RAISE NOTICE 'Tabla: %', table_obj.tablename;
         if table_obj.sequencename is not null and table_obj.tablename like table_obj.columnname then
                  execute 'SELECT setval('|| quote_literal(table_obj.sequencename) ||', (SELECT max('|| table_obj.columnname || ')+1 FROM '|| table_obj.tablename ||'));';
         end if;
     end loop;
     return 1;
end;
$body$
LANGUAGE 'plpgsql' IMMUTABLE RETURNS NULL ON NULL INPUT SECURITY INVOKER;

Hope it's usefull.

Reference:
http://archives.postgresql.org/pgsql-admin/2005-03/msg00011.php

César Herrera
http://www.tsandu.com
México