[SQL] pg_dump : problem with grant on table columns
Hello,
I sometimes define some access rights on table columns instead of whole
table. Everything works fine except when I perform a dump.
When I dump a databse that contains such access rights, the pg_dump utility
generates commands like this one :
GRANT SELECT("Login") ON TABLE "Clients" TO "AgendaSubscript";
But, when I execute the content of this dump in a fresh database (for
backup and restore stuff), those commands are rejected and thus, my
restored database has not the proper access rights.
Is this a bug with the pg_dump tool or am I doing something wrong ?
Regards,
Brice André
[SQL] updating a sequence
Hi, I have need of a statement that updates the sequence but uses a max() to find the number. alter sequence somename restart with (select max(pk) from sometable). I need this for automating an ETL (using pentaho). Postgres 8.4 Thanks in advance, Johnf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] updating a sequence
On Tue, Nov 15, 2011 at 8:06 AM, John Fabiani wrote: > alter sequence somename restart with (select max(pk) from sometable). > > I need this for automating an ETL (using pentaho). http://www.postgresql.org/docs/9.1/interactive/functions-sequence.html#FUNCTIONS-SEQUENCE-TABLE -- Regards, Richard Broersma Jr. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pg_dump : problem with grant on table columns
On Nov 15, 2011 9:46 PM, "Brice André" wrote:
>
> Hello,
>
> I sometimes define some access rights on table columns instead of whole
table. Everything works fine except when I perform a dump.
>
> When I dump a databse that contains such access rights, the pg_dump
utility generates commands like this one :
> GRANT SELECT("Login") ON TABLE "Clients" TO "AgendaSubscript";
>
> But, when I execute the content of this dump in a fresh database (for
backup and restore stuff), those commands are rejected and thus, my
restored database has not the proper access rights.
Please specify the version of Pg you are dumping from and the version you
are restoring to.
At a guess you are restoring to a version from before column grants were
added.
Please also give the full, exact text of any error message you get. See the
guide to reporting problems on the wiki.
>
> Is this a bug with the pg_dump tool or am I doing something wrong ?
>
> Regards,
>
> Brice André
Re: [SQL] updating a sequence
On Tuesday, November 15, 2011 08:33:54 am Richard Broersma wrote: > On Tue, Nov 15, 2011 at 8:06 AM, John Fabiani wrote: > > alter sequence somename restart with (select max(pk) from sometable). > > > > I need this for automating an ETL (using pentaho). > > http://www.postgresql.org/docs/9.1/interactive/functions-sequence.html#FUNC > TIONS-SEQUENCE-TABLE I don't see how that helps answer my problem. I know how to update a sequence. I want to pass a value for the restart vaue that depends on a query - all in one statement. I would think it is a common problem i.e. migrating data. Thanks, Johnf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] updating a sequence
On Tue, Nov 15, 2011 at 4:28 PM, John Fabiani wrote:
> On Tuesday, November 15, 2011 08:33:54 am Richard Broersma wrote:
> > On Tue, Nov 15, 2011 at 8:06 AM, John Fabiani
> wrote:
> > > alter sequence somename restart with (select max(pk) from sometable).
> > >
> > > I need this for automating an ETL (using pentaho).
> >
> >
> http://www.postgresql.org/docs/9.1/interactive/functions-sequence.html#FUNC
> > TIONS-SEQUENCE-TABLE
>
>
> I don't see how that helps answer my problem. I know how to update a
> sequence. I want to pass a value for the restart vaue that depends on a
> query
> - all in one statement. I would think it is a common problem i.e.
> migrating
> data.
>
>
use a subquery to set the value -
select setval('foo', select max(some_id) from some_table)
It's all right there in the docs that you were pointed to. We try to
encourage people to be somewhat self sufficient around here.
Re: [SQL] updating a sequence
On Tue, Nov 15, 2011 at 5:33 PM, Samuel Gendler
wrote:
> On Tue, Nov 15, 2011 at 4:28 PM, John Fabiani wrote:
>>
>> On Tuesday, November 15, 2011 08:33:54 am Richard Broersma wrote:
>> > On Tue, Nov 15, 2011 at 8:06 AM, John Fabiani
>> > wrote:
>> > > alter sequence somename restart with (select max(pk) from sometable).
>> > >
>> > > I need this for automating an ETL (using pentaho).
>> >
>> >
>> > http://www.postgresql.org/docs/9.1/interactive/functions-sequence.html#FUNC
>> > TIONS-SEQUENCE-TABLE
>>
>>
>> I don't see how that helps answer my problem. I know how to update a
>> sequence. I want to pass a value for the restart vaue that depends on a
>> query
>> - all in one statement. I would think it is a common problem i.e.
>> migrating
>> data.
>>
>
> use a subquery to set the value -
> select setval('foo', select max(some_id) from some_table)
> It's all right there in the docs that you were pointed to. We try to
> encourage people to be somewhat self sufficient around here.
You need to wrap a subselect in ():
select setval('foo', (select max(some_id) from some_table));
That works in 9.1.1. No clue about previous versions off the top of
my head, but I seem to recall it doesn't work in 8.3 and prior
versions.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] updating a sequence
On Tue, Nov 15, 2011 at 8:46 PM, Scott Marlowe wrote:
> On Tue, Nov 15, 2011 at 5:33 PM, Samuel Gendler
> wrote:
>> On Tue, Nov 15, 2011 at 4:28 PM, John Fabiani wrote:
>>>
>>> On Tuesday, November 15, 2011 08:33:54 am Richard Broersma wrote:
>>> > On Tue, Nov 15, 2011 at 8:06 AM, John Fabiani
>>> > wrote:
>>> > > alter sequence somename restart with (select max(pk) from sometable).
>>> > >
>>> > > I need this for automating an ETL (using pentaho).
>>> >
>>> >
>>> > http://www.postgresql.org/docs/9.1/interactive/functions-sequence.html#FUNC
>>> > TIONS-SEQUENCE-TABLE
>>>
>>>
>>> I don't see how that helps answer my problem. I know how to update a
>>> sequence. I want to pass a value for the restart vaue that depends on a
>>> query
>>> - all in one statement. I would think it is a common problem i.e.
>>> migrating
>>> data.
>>>
>>
>> use a subquery to set the value -
>> select setval('foo', select max(some_id) from some_table)
>> It's all right there in the with docs that you were pointed to. We try to
>> encourage people to be somewhat self sufficient around here.
>
> You need to wrap a subselect in ():
>
> select setval('foo', (select max(some_id) from some_table));
>
> That works in 9.1.1. No clue about previous versions off the top of
> my head, but I seem to recall it doesn't work in 8.3 and prior
> versions.
Was wrong, it definitely works in 8.3. But only with the parens.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] updating a sequence
Sent from my iPhone
On Nov 15, 2011, at 7:49 PM, Scott Marlowe wrote:
> On Tue, Nov 15, 2011 at 8:46 PM, Scott Marlowe
> wrote:
>> On Tue, Nov 15, 2011 at 5:33 PM, Samuel Gendler
>> wrote:
>>> On Tue, Nov 15, 2011 at 4:28 PM, John Fabiani wrote:
On Tuesday, November 15, 2011 08:33:54 am Richard Broersma wrote:
> On Tue, Nov 15, 2011 at 8:06 AM, John Fabiani
> wrote:
>> alter sequence somename restart with (select max(pk) from sometable).
>>
>> I need this for automating an ETL (using pentaho).
>
>
> http://www.postgresql.org/docs/9.1/interactive/functions-sequence.html#FUNC
> TIONS-SEQUENCE-TABLE
I don't see how that helps answer my problem. I know how to update a
sequence. I want to pass a value for the restart vaue that depends on a
query
- all in one statement. I would think it is a common problem i.e.
migrating
data.
>>>
>>> use a subquery to set the value -
>>> select setval('foo', select max(some_id) from some_table)
>>> It's all right there in the with docs that you were pointed to. We try to
>>> encourage people to be somewhat self sufficient around here.
>>
>> You need to wrap a subselect in ():
>>
>> select setval('foo', (select max(some_id) from some_table));
>>
>> That works in 9.1.1. No clue about previous versions off the top of
>> my head, but I seem to recall it doesn't work in 8.3 and prior
>> versions.
>
> Was wrong, it definitely works in 8.3. But only with the parens.
Yes. My version without parens was just a typo. I didn't test it before sending
it
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
