Обсуждение: getting a list of users

All,

How do I get a list of database usernames using the postgres C API?

Thanks,
Eric

Never mind, everyone.  I figured it out.

On May 7, 2009, at 8:05 PM, Eric Smith wrote:

> All,
>
> How do I get a list of database usernames using the postgres C API?
>
> Thanks,
> Eric
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Eric Smith wrote:
> How do I get a list of database usernames using the postgres C API?

Execute this query:

   SELECT usename FROM pg_catalog.pg_user

and read the results.

Yours,
Laurenz Albe

All,

When I try the command below, I get the very familiar error: "expected
just one rule action"

I'm running 8.3.5 on Mac OS 10.5.6.

Any ideas?

Thanks,
Eric

On May 7, 2009, at 11:24 PM, Albe Laurenz wrote:

> Eric Smith wrote:
>> How do I get a list of database usernames using the postgres C API?
>
> Execute this query:
>
>   SELECT usename FROM pg_catalog.pg_user
>
> and read the results.
>
> Yours,
> Laurenz Albe
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Eric Smith <eric_h_smith@mac.com> writes:
> When I try the command below, I get the very familiar error: "expected
> just one rule action"

You mean this?

regression=# SELECT usename FROM pg_catalog.pg_user;
 usename
----------
 postgres
(1 row)


If you're getting that sort of error from standard views then there is
something exceedingly broken about your Postgres build.  We have
occasionally heard from people who got similar errors after installing
nonstandard hacks that modify the representation of query trees ...
so is this a completely vanilla build?  Where did you get it from?

            regards, tom lane

Yep, using that command gives me the error.

I'm using a build that came from the postgres website, and just uses
the config that comes with it.

Eric


On May 9, 2009, at 5:16 PM, Tom Lane wrote:

> Eric Smith <eric_h_smith@mac.com> writes:
>> When I try the command below, I get the very familiar error:
>> "expected
>> just one rule action"
>
> You mean this?
>
> regression=# SELECT usename FROM pg_catalog.pg_user;
> usename
> ----------
> postgres
> (1 row)
>
>
> If you're getting that sort of error from standard views then there is
> something exceedingly broken about your Postgres build.  We have
> occasionally heard from people who got similar errors after installing
> nonstandard hacks that modify the representation of query trees ...
> so is this a completely vanilla build?  Where did you get it from?
>
>             regards, tom lane

Eric Smith <eric_h_smith@mac.com> writes:
> Yep, using that command gives me the error.
> I'm using a build that came from the postgres website, and just uses
> the config that comes with it.

Hmm, you mean the EDB one-click installer?  I don't offhand see another
binary distro for OSX there.

It seems unlikely that the EDB build would be that broken on its own
terms, but maybe you've run into some incompatibility between different
releases of their build, or maybe you're trying to use their build
against a database that was initdb'd with some other build.  Have you
upgraded Postgres since you ran initdb?  If so, which version exactly
did you initdb with?

            regards, tom lane

Eric Smith <eric_h_smith@mac.com> writes:
> I actually started with the source: postgresql-8.3.5.tar.gz, from the
> source download location.  I just build straight from that source...
> no mucking around with any of the sources.

Hmph, now I'm really mystified.  If the straight source build were
this broken we'd surely have noticed (we have multiple Mac buildfarm
machines, not to mention various developers including me who test
regularly on Macs).  Have you tried running the regression tests
against your build?  Could you provide the output of pg_config?

            regards, tom lane

You bet... here you go.

Thanks,
Eric



On May 9, 2009, at 6:08 PM, Tom Lane wrote:

> Eric Smith <eric_h_smith@mac.com> writes:
>> I actually started with the source: postgresql-8.3.5.tar.gz, from the
>> source download location.  I just build straight from that source...
>> no mucking around with any of the sources.
>
> Hmph, now I'm really mystified.  If the straight source build were
> this broken we'd surely have noticed (we have multiple Mac buildfarm
> machines, not to mention various developers including me who test
> regularly on Macs).  Have you tried running the regression tests
> against your build?  Could you provide the output of pg_config?
>
>             regards, tom lane

Eric Smith <eric_h_smith@mac.com> writes:
> You bet... here you go.

Hmm, I see you are trying to build universal binaries:
> CFLAGS=-arch i386 -arch ppc ...

That isn't exactly a trivial thing to do, because the pg_config.h data
differs for the two arches.  It will *not* work to just run a basic
configure and build with CFLAGS set like that.  (If you troll the
pgsql-archives archives for "universal binary" you can probably
find some discussions of what's needed to make it work.  I seem to
recall that we simplified it in the last year or so, but that was
very possibly post-8.3.)

I'm not immediately sure that the symptom you mention would be the most
obvious failure, but I'm suspicious.  Did you take measures to try to
make the universal build actually work, or did you just build with
these switches?  Which arch are you actually building and/or
running on?

            regards, tom lane

I wrote:
> That isn't exactly a trivial thing to do, because the pg_config.h data
> differs for the two arches.  It will *not* work to just run a basic
> configure and build with CFLAGS set like that.  (If you troll the
> pgsql-archives archives for "universal binary" you can probably
> find some discussions of what's needed to make it work.  I seem to
> recall that we simplified it in the last year or so, but that was
> very possibly post-8.3.)

er ... s/pgsql-archives/pgsql-hackers/ of course ... a bit of trolling
later, it seems that this message has the best summary of what you have
to do with 8.3 or older branches to get working universal binaries:

http://archives.postgresql.org/pgsql-general/2008-02/msg00200.php

Things will be less ugly with 8.4:

http://archives.postgresql.org/pgsql-hackers/2008-07/msg00884.php

but still not just a quick CFLAGS setting away.

But it remains to be determined whether that's actually the cause of the
view problem you're complaining about.  Did you configure/build on a
different arch than you're running on now?

            regards, tom lane

Tom,

You are correct.  On an Intel, the failed command I mentioned earlier
works just fine.

I'm building for, and running on, both PPC and Intel.  I've been able
to avoid these snags in the past, but I'm now adding user management
to the app, and I'm dead in the water on the PPC.  I'll look through
the archives as you suggest.

Regards,
Eric

On May 9, 2009, at 6:37 PM, Tom Lane wrote:

> Eric Smith <eric_h_smith@mac.com> writes:
>> You bet... here you go.
>
> Hmm, I see you are trying to build universal binaries:
>> CFLAGS=-arch i386 -arch ppc ...
>
> That isn't exactly a trivial thing to do, because the pg_config.h data
> differs for the two arches.  It will *not* work to just run a basic
> configure and build with CFLAGS set like that.  (If you troll the
> pgsql-archives archives for "universal binary" you can probably
> find some discussions of what's needed to make it work.  I seem to
> recall that we simplified it in the last year or so, but that was
> very possibly post-8.3.)
>
> I'm not immediately sure that the symptom you mention would be the
> most
> obvious failure, but I'm suspicious.  Did you take measures to try to
> make the universal build actually work, or did you just build with
> these switches?  Which arch are you actually building and/or
> running on?
>
>             regards, tom lane

Tom,

Thanks for the detailed info... makes my life a lot easier!

Eric

On May 9, 2009, at 7:07 PM, Tom Lane wrote:

> I wrote:
>> That isn't exactly a trivial thing to do, because the pg_config.h
>> data
>> differs for the two arches.  It will *not* work to just run a basic
>> configure and build with CFLAGS set like that.  (If you troll the
>> pgsql-archives archives for "universal binary" you can probably
>> find some discussions of what's needed to make it work.  I seem to
>> recall that we simplified it in the last year or so, but that was
>> very possibly post-8.3.)
>
> er ... s/pgsql-archives/pgsql-hackers/ of course ... a bit of trolling
> later, it seems that this message has the best summary of what you
> have
> to do with 8.3 or older branches to get working universal binaries:
>
> http://archives.postgresql.org/pgsql-general/2008-02/msg00200.php
>
> Things will be less ugly with 8.4:
>
> http://archives.postgresql.org/pgsql-hackers/2008-07/msg00884.php
>
> but still not just a quick CFLAGS setting away.
>
> But it remains to be determined whether that's actually the cause of
> the
> view problem you're complaining about.  Did you configure/build on a
> different arch than you're running on now?
>
>             regards, tom lane

Eric Smith <eric_h_smith@mac.com> writes:
> You are correct.  On an Intel, the failed command I mentioned earlier
> works just fine.

> I'm building for, and running on, both PPC and Intel.  I've been able
> to avoid these snags in the past, but I'm now adding user management
> to the app, and I'm dead in the water on the PPC.

Just for fun, I tried to reproduce this.  I couldn't figure out what
you'd done to build a dual-arch version of 8.3 --- it certainly would
take more than just passing some funny FLAGS settings to configure.
With CVS HEAD though, the build *does* go through with only a couple
of minor bleats from ranlib, given

CFLAGS='-arch i386 -arch ppc'  LDFLAGS='-arch i386 -arch ppc' configure ...

Building this way on x86 OSX 10.5.6, the resulting binaries work just
fine (of course) on x86.  Not so much on PPC --- I'm surprised it even
gets through initdb, but it does, and I was able to reproduce your
"expected just one rule action".  The regression tests fail rather
miserably though.

Comparing the configure output files, it seems that Apple's compiler
uses the same alignment rules for x86 and ppc, so that the only actual
pg_config.h difference is WORDS_BIGENDIAN.  Which means this probably
would have worked all right in pre-8.3 versions of PG.  But the rules
for short datum headers in 8.3+ will not work at all if the machine
doesn't have the endianness the code thinks.  It looks like the reason
for "expected just one rule action" is that most of the
pg_rewrite.ev_action entries read out as empty C strings --- they are
really text datums of more than 127 bytes and the tuple disaassembly
code is misinterpreting their length words.  I suppose if your app
doesn't deal in fields that are wider than 127 bytes on-disk, you
might have managed to miss realizing that the build was entirely
busted...

So the bottom line seems to be that you need to modify your custom build
procedure to allow for architecture-specific pg_config.h.

            regards, tom lane

Fyi, this is exactly what i saw in my first attempts to build a
universal binary. Running configure for each architecture and hacking
up pg_config.h (per one of the archives links you posted previously)
does fix it, though iirc, 32 & 64 bit builds in the same binary will
take some more work.

On 5/10/09, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Eric Smith <eric_h_smith@mac.com> writes:
>> You are correct.  On an Intel, the failed command I mentioned earlier
>> works just fine.
>
>> I'm building for, and running on, both PPC and Intel.  I've been able
>> to avoid these snags in the past, but I'm now adding user management
>> to the app, and I'm dead in the water on the PPC.
>
> Just for fun, I tried to reproduce this.  I couldn't figure out what
> you'd done to build a dual-arch version of 8.3 --- it certainly would
> take more than just passing some funny FLAGS settings to configure.
> With CVS HEAD though, the build *does* go through with only a couple
> of minor bleats from ranlib, given
>
> CFLAGS='-arch i386 -arch ppc'  LDFLAGS='-arch i386 -arch ppc' configure ...
>
> Building this way on x86 OSX 10.5.6, the resulting binaries work just
> fine (of course) on x86.  Not so much on PPC --- I'm surprised it even
> gets through initdb, but it does, and I was able to reproduce your
> "expected just one rule action".  The regression tests fail rather
> miserably though.
>
> Comparing the configure output files, it seems that Apple's compiler
> uses the same alignment rules for x86 and ppc, so that the only actual
> pg_config.h difference is WORDS_BIGENDIAN.  Which means this probably
> would have worked all right in pre-8.3 versions of PG.  But the rules
> for short datum headers in 8.3+ will not work at all if the machine
> doesn't have the endianness the code thinks.  It looks like the reason
> for "expected just one rule action" is that most of the
> pg_rewrite.ev_action entries read out as empty C strings --- they are
> really text datums of more than 127 bytes and the tuple disaassembly
> code is misinterpreting their length words.  I suppose if your app
> doesn't deal in fields that are wider than 127 bytes on-disk, you
> might have managed to miss realizing that the build was entirely
> busted...
>
> So the bottom line seems to be that you need to modify your custom build
> procedure to allow for architecture-specific pg_config.h.
>
>             regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


--
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

Eric Smith <eric_h_smith@mac.com> writes:
> Seems to work just fine.  If, on the other hand, I issue the same
> command from within the C API:

> NSString* query = [NSString stringWithFormat:@"SELECT usename FROM
> pg_catalog.pg_user"];
>     postgresResult = PQexec(myConnection, [query UTF8String]);

> I get an error, extracted via:

You sure that thing is sending the string you think it is?  I'd
try turning on log_statement and looking in the postmaster log
to see what it thinks it's getting.

            regards, tom lane

Alrighty.... so, how do I turn on log_statement?

Thanks,
Eric

On May 25, 2009, at 5:31 PM, Tom Lane wrote:

> Eric Smith <eric_h_smith@mac.com> writes:
>> Seems to work just fine.  If, on the other hand, I issue the same
>> command from within the C API:
>
>> NSString* query = [NSString stringWithFormat:@"SELECT usename FROM
>> pg_catalog.pg_user"];
>>     postgresResult = PQexec(myConnection, [query UTF8String]);
>
>> I get an error, extracted via:
>
> You sure that thing is sending the string you think it is?  I'd
> try turning on log_statement and looking in the postmaster log
> to see what it thinks it's getting.
>
>             regards, tom lane

Very interesting.

when I set log_statement='all', I see entries for various queries, but
I see nothing for the query I list below.  If I just use printf() to
print out the c-string, it looks just like all the other queries.

Eric

On May 25, 2009, at 5:31 PM, Tom Lane wrote:

> Eric Smith <eric_h_smith@mac.com> writes:
>> Seems to work just fine.  If, on the other hand, I issue the same
>> command from within the C API:
>
>> NSString* query = [NSString stringWithFormat:@"SELECT usename FROM
>> pg_catalog.pg_user"];
>>     postgresResult = PQexec(myConnection, [query UTF8String]);
>
>> I get an error, extracted via:
>
> You sure that thing is sending the string you think it is?  I'd
> try turning on log_statement and looking in the postmaster log
> to see what it thinks it's getting.
>
>             regards, tom lane

OK... never mind, everyone.  The problem was elsewhere in the code.

Regards,
Eric

On May 25, 2009, at 5:31 PM, Tom Lane wrote:

> Eric Smith <eric_h_smith@mac.com> writes:
>> Seems to work just fine.  If, on the other hand, I issue the same
>> command from within the C API:
>
>> NSString* query = [NSString stringWithFormat:@"SELECT usename FROM
>> pg_catalog.pg_user"];
>>     postgresResult = PQexec(myConnection, [query UTF8String]);
>
>> I get an error, extracted via:
>
> You sure that thing is sending the string you think it is?  I'd
> try turning on log_statement and looking in the postmaster log
> to see what it thinks it's getting.
>
>             regards, tom lane