Re: Schema problems RedHat / SuSE 9.3 for version 7.4.8

Hi list,

Again after reconsidering the way this problem came to be, it struck me that I
used a pg_dump -f /tmp/dumpdb and restored with psql. How can it be that not
all grants of the schema's, views and tables are restored? There were no
messages during the restore that could be interpreted as an indication that
the grants were not restored.

Op vrijdag 2 september 2005 14:35, schreef Dick Kniep:
> Hi list/Michael,
>
> Sorry I forgot "reply to all"
>
> It proved to be a problem with the permissions on the table and view! So,
> the error that was reported was completely different from the actual error.
> I do not know how this can happen, but by making a direct connection to the
> database within Zope, I was able to get the real error message.
>
> I will investigate further how the reporting of the messages got confused.
> It could be a problem in Zope or in psycopg. If I find something
> interesting I will report back to the list.
>
> Thanks for the help.
>
> Dick
>
> Op vrijdag 2 september 2005 00:04, schreef Michael Fuhr:
> > [Please copy the mailing list on replies so others can contribute
> > to and learn from the discussion. I've quoted more of your message
> > than I ordinarily would because other people won't have seen it and
> > they won't find it in the list archives.]
> >
> > On Thu, Sep 01, 2005 at 11:35:43PM +0200, Dick Kniep wrote:
> > > After starting psql, and executing the query, without a begin, after
> > > the query there is no search path
> > >
> > > SELECT set_config('search_path', '"' || t2.schema || '"', true) FROM
> > > "Lindix"."Gebruikers" as t1, "Lindix"."Administratie" as t2 WHERE uid =
> > > 'zon0023' AND t1.administratie_id = t2.administratie_id;
> > > set_config
> > > ------------------
> > > "adeuxproductie"
> > > (1 row)
> > >
> > > cvix=# SHOW search_path;
> > > search_path
> > > --------------
> > > $user,public
> > > (1 row)
> >
> > Apparently you're in autocommit mode, which is the default for psql.
> > Each statement is its own transaction, so you won't see the effects
> > of set_config() when the third argument is true.
> >
> > > Executed with third parameter false:
> > >
> > > cvix=# SELECT set_config('search_path', '"' || t2.schema || '"', false)
> > > FROM "Lindix"."Gebruikers" as t1, "Lindix"."Administratie" as t2 WHERE
> > > uid = 'zon0023' AND t1.administratie_id = t2.administratie_id;
> > > set_config
> > > ------------------
> > > "adeuxproductie"
> > > (1 row)
> > >
> > > cvix=# SHOW search_path;
> > > search_path
> > > ------------------
> > > "adeuxproductie"
> > > (1 row)
> > >
> > > Also the same result when I have a "begin" before the first statement.
> > > Which means that it seems to work correctly!
> >
> > Yep. If you're in a transaction block, or if you tell set_config()
> > not to make the change local to the transaction, then you see the
> > new setting take effect.
> >
> > > Also, a thing I hadn't checked before, is that the psql results on the
> > > 2 servers are the same. Which leads to my conclusion that the
> > > autocommit settings are indeed different on the 2 servers.
> >
> > What do "SELECT version()" and "SHOW autocommit" show on both
> > servers? If both servers are running 7.4 then they can't have
> > different autocommit settings because 7.4 and later don't support
> > server-side autocommit (it always shows "on" and you can't change
> > it). Unless one of the servers is running 7.3, the autocommit
> > settings must be on the client side. Are you using the same instance
> > of the client to connect to both servers?
> >
> > > OK, next question, how do I get rid of the autocommit in my
> > > application? I tried set autocommit to off; but that is deprecated.
> >
> > Using "SET autocommit" attempts to change the server-side setting,
> > which was only supported in 7.3 (the developers removed it after
> > deciding it had been a bad idea). How to disable autocommit on the
> > client side depends on your client interface. What language and
> > API are you using?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster