Wikimedia Bugzilla is closed!

Another schema breakage from Werdna...

The PostgreSQL initial query is incomplete, it lacks pr_id and the sequence. Try
dropping the page_restrictions table and running the following query perhaps:

CREATE SEQUENCE pr_id_val;
CREATE TABLE page_restrictions (
  pr_id      INTEGER  NOT NULL  PRIMARY KEY DEFAULT nextval('pr_id_val'),
  pr_page    INTEGER          NULL  REFERENCES page (page_id) ON DELETE CASCADE,
  pr_type    TEXT         NOT NULL,
  pr_level   TEXT         NOT NULL,
  pr_cascade SMALLINT     NOT NULL,
  pr_user    INTEGER          NULL,
  pr_expiry  TIMESTAMPTZ      NULL
);

Currently we have an ALTER query afterwords:

ALTER TABLE page_restrictions ADD CONSTRAINT page_restrictions_pk PRIMARY KEY
(pr_page,pr_type);

Though I am not sure why that contraint is added to PostgreSQL schemas while no
pr_id is added.

Fixed in r20053.

Thanks for looking into it, but it is not yet fixed:

1)
You forgot to put column
    pr_pagetype text
in tables.sql

2)
with above mentionned column added it is possible to protect pages, but going to
SpecialPages:Protected Pages gives now:

A database error has occurred Query: SELECT page_id, page_restrictions.*,
page_title,page_namespace FROM page_restrictions,page WHERE
(pr_expiry>'2007-02-25 20:28:58 GMT') AND (page_id=pr_page) GROUP BY page_id
ORDER BY pr_id DESC LIMIT 51 Function: IndexPager::reallyDoQuery
(ProtectedPagesPager) Error: 1 ERROR: column "page_restrictions.pr_id" must
appear in the GROUP BY clause or be used in an aggregate function

Backtrace:

#0 /var/www/wiki/includes/Database.php(677):
DatabasePostgres->reportQueryError('ERROR: column ...', 1, 'SELECT page_id...',
'IndexPager::rea...', false)
#1 /var/www/wiki/includes/Database.php(1073): Database->query('SELECT
page_id...', 'IndexPager::rea...')
#2 /var/www/wiki/includes/Pager.php(184): Database->select(Array, 'page_id,
page_r...', Array, 'IndexPager::rea...', Array)
#3 /var/www/wiki/includes/Pager.php(102): IndexPager->reallyDoQuery('', 51, false)
#4 /var/www/wiki/includes/Pager.php(296): IndexPager->doQuery()
#5 /var/www/wiki/includes/SpecialProtectedpages.php(27): IndexPager->getNumRows()
#6 /var/www/wiki/includes/SpecialProtectedpages.php(137):
ProtectedPagesForm->showList()
#7 /var/www/wiki/includes/SpecialPage.php(624): wfSpecialProtectedpages(NULL,
Object(SpecialPage))
#8 /var/www/wiki/includes/SpecialPage.php(430): SpecialPage->execute(NULL)
#9 /var/www/wiki/includes/Wiki.php(180): SpecialPage::executePath(Object(Title))
#10 /var/www/wiki/includes/Wiki.php(45):
MediaWiki->initializeSpecialCases(Object(Title), Object(OutputPage),
Object(WebRequest))
#11 /var/www/wiki/index.php(48): MediaWiki->initialize(Object(Title),
Object(OutputPage), Object(User), Object(WebRequest))
#12 {main}

1) pr_pagetype is bug 9097, I'll see if I can look at that later.

2) see http://archives.postgresql.org/pgsql-general/2004-02/msg01199.php

It seems like this could occure with pr_id values that are the same, but since
the query should list it as UNIQUE, that should not happen. Did you view your
table, is this the case? If not, I suppose we can use max(pr_id) or DISTINCT ON,
but it still seems odd.

I'm still not quite clear on why we are using pr_id at all. If it's merely to
allow the display to order by time added, why not make the new column a
timestamp field instead? (The table already has a primary key).

(In reply to comment #6)
> I'm still not quite clear on why we are using pr_id at all. If it's merely to
> allow the display to order by time added, why not make the new column a
> timestamp field instead? (The table already has a primary key).

Because the field was introduced AFTER the table was first created; and I didn't
want to necessitate pre-filling a timestamp field with bogus data. With pr_id,
SQL (mysql, at least) pre-filled the data field with autonumbering when the
update was first run.

In retrospect, I could have run IN the updater 'UPDATE page_restrictions SET
pr_timestamp=CURRENT_TIMESTAMP', but I don't see the point in changing this now.

Unassigning. I don't have time to sort this out at the moment - my academic work
takes priority over volunteer development.

Thanks for the explanation, that makes sense. I'll see if I can figure out
exactly what the page is trying to show and modify it for Postgres next.

Fixed in 20560, although it may have to be separated for mysql optimization, so
not resolving this yet.

Hearing no objections, marking this as resolved.

Reopening since it not seems to be (completely?) resolved:
To avoid the error it is necessary to have a column pr_id in table
page_restrictions. AFAIS this is now contained in
/wiki/maintenance/mysql5/tables.sql as   
  -- Field for an ID for this restrictions row (sort-key for
Special:ProtectedPages)                                                        
                                                 
  pr_id int unsigned NOT NULL auto_increment

but it is not in /wiki/maintenance/postgres/tables.sql .

The definition would be:
ALTER TABLE page_restrictions ADD COLUMN pr_id int4;
ALTER TABLE page_restrictions ALTER COLUMN pr_id SET STORAGE PLAIN;
ALTER TABLE page_restrictions ALTER COLUMN pr_id SET NOT NULL;
ALTER TABLE page_restrictions ALTER COLUMN pr_id SET DEFAULT
nextval('page_restrictions_pr_id_seq'::regclass);

and is working for me this way.

> To avoid the error it is necessary to have a column pr_id in table
...
> but it is not in /wiki/maintenance/postgres/tables.sql .

Sure it is, line 116. Added as of comment #3 above.

You are right, sorry for the noise!