Selects in server side functions

Hi,

I'm getting this weird error when using the \d or \dd command. This same
error also pops up when I try to do anything in pgaccess. This is PostgreSQL
7.0.2 on LinuxPPC. I tried wiping out the whole installation, reinstalling,
rebooting the whole machine, starting with a fresh install and fresh
database. Still I can't seem to get this error to go away. I don't see
anything about this error in the manuals (I did a full text search!).

Here's a little transcript of the action. I created a database called test1,
a new table, then added a few rows. So it seems like I can hobble by as long
as I don't use pgaccess or the \d command with an argument ... but it would
be nice to use them (and know everything's right). Thanks for any help...

\/---------transcript:--------------\/

$ psql test1
Welcome to psql, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

test1=# \d
List of relations
Name | Type | Owner
---------------+----------+----------
stuff | table | user1
stuff_sid_seq | sequence | user1
(2 rows)

test1=# \d stuff
ERROR: getattproperties: no attribute tuple 1259 -2
test1=# \dd stuff
ERROR: get_attdisbursion: no attribute tuple 16932 -2
test1=# insert into stuff (stuffhere) values ('here is some stuff.');
INSERT 19071 1
test1=# insert into stuff (stuffhere) values ('here is some more stuff.');
INSERT 19072 1
test1=# select * from stuff;
sid | stuffhere
-----+--------------------------
1 | here is some stuff.
2 | here is some more stuff.
(2 rows)

test1=# \d stuff
ERROR: getattproperties: no attribute tuple 1259 -2

"Neil Davis" <npdavis@hotmail.com> writes:

I am trying to figure out how to return more than one field using "SETOF".
I can concatenate the fields in the select statement down to one and
use SETOF Text to return them, then split them back apart in my
code.

SETOF is not for returning multiple columns, it is for returning
multiple *rows*. I don't think that's what you want here.

Returning multiple columns requires a tuple (structure) return datatype.
Unfortunately that's not supported all that well right now. Check the
pgsql archives for my message about functions returning tuples, from a
few days back.

regards, tom lane

On Fri, 27 Oct 2000, Neil Davis wrote:

Hello,
I am trying to figure out how to return more than one field using "SETOF".

SETOF is to return more than one record, not more than one field. Also,
SETOF is only currently working for SQL language functions.

What you probably need is following:
create procedure x returns foo ....

and create a table foo with list of fields you want to return.

That will work IFF you are not calling this function directly from the
client, but are accessing it from plpgsql code. If you are accessing it
from a client, it won't work. I don't know what will :)

Isaac <Isaac@UNSTOPPABLE.com> writes:

ERROR: getattproperties: no attribute tuple 1259 -2

Postgres 7.0.* doesn't work on PPC unless compiled -O0. The above is
a typical symptom of being compiled with higher optimization settings.
Unfortunately, it seems that our PPC RPMs for 7.0.2 were compiled with
the wrong -O level :-(. (Don't ask me why the RPMs ended up that way
when a clean source compilation uses -O0, but there it is.)

We'll try to do better with 7.0.3, but in the meantime try compiling
the source distribution from scratch.

FWIW, 7.1 will not have this weird problem with optimization on PPC.

regards, tom lane

Awesome, thanx. I thought i was doing something wrong. I suggest that some
sort of warning about this go on the pgsql website (and other places where
us PPC types will notice it) so no one else has to beat their head on the
wall over this.

AP2P
--i

on 10/27/00 10:52 PM, Tom Lane at tgl@sss.pgh.pa.us wrote:

set digest pgsql-general

Isaac and Tom,

Awesome, thanx. I thought i was doing something wrong. I suggest that some
sort of warning about this go on the pgsql website (and other places where
us PPC types will notice it) so no one else has to beat their head on the
wall over this.

Postgres 7.0.* doesn't work on PPC unless compiled -O0. The above is
a typical symptom of being compiled with higher optimization settings.
Unfortunately, it seems that our PPC RPMs for 7.0.2 were compiled with
the wrong -O level :-(. (Don't ask me why the RPMs ended up that way
when a clean source compilation uses -O0, but there it is.)

Why is this? We're running 7.0.2 and have not performed any of these
compile-time gymnastics...and we're not having any problems (although we've
barely scratched the surface of what PostgreSQL can do). We're running
Yellow Dog Linux Champion Server 1.2 on a 450Mhz PowerPC G4.

By the way- does anybody know of any resources related to securing
databases. We'd like to only allow certain users access to each database,
but cannot find the appropriate section in any of the documentation pages.

Thank you for your time and assistance.

Sincerely,

Robert Vogt IV
CEO
ArborHost

* Robert Vogt IV <vogt@arborhost.com> [001028 12:10]:
[SNIP]

By the way- does anybody know of any resources related to securing
databases. We'd like to only allow certain users access to each database,
but cannot find the appropriate section in any of the documentation pages.

Look at pg_hba.conf in your data directory. Here is mine from 7.0.2:
# cat pg_hba.conf
#
# Example PostgreSQL host access control file.
#
#
# This file controls what hosts are allowed to connect to what
# databases
# and specifies some options on how users on a particular host are
# identified.
# It is read each time a host tries to make a connection to a
# database.
#
# Each line (terminated by a newline character) is a record. A record
# cannot
# be continued across two lines.
#
# There are 3 kinds of records:
#
# 1) comment: Starts with #.
#
# 2) empty: Contains nothing excepting spaces and tabs.
#
# 3) content: anything else.
#
# Unless specified otherwise, "record" from here on means a content
# record.
#
# A record consists of tokens separated by spaces or tabs. Spaces and
# tabs at the beginning and end of a record are ignored as are extra
# spaces and tabs between two tokens.
#
# The first token in a record is the record type. The interpretation
# of the
# rest of the record depends on the record type.
#
# Record type "host"
# ------------------
#
# This record identifies a set of network hosts that are permitted to
# connect
# to databases. No network hosts are permitted to connect except as
# specified
# by a "host" record. See the record type "local" to specify
# permitted
# connections using UNIX sockets.
#
# Format:
#
# host DBNAME IP_ADDRESS ADDRESS_MASK USERAUTH [AUTH_ARGUMENT]
#
# DBNAME is the name of a PostgreSQL database, "all" to indicate all
# databases, or "sameuser" to restrict a user's access to a database
# with the same user name.
#
# IP_ADDRESS and ADDRESS_MASK are a standard dotted decimal IP address
# and
# mask to identify a set of hosts. These hosts are allowed to connect
# to
# Database DBNAME.
#
# USERAUTH is a keyword indicating the method used to authenticate the
# user, i.e. to determine that the principal is authorized to connect
# under the PostgreSQL username he supplies in his connection
# parameters.
#
# ident: Authentication is done by the ident server on the remote
# host, via the ident (RFC 1413) protocol. AUTH_ARGUMENT,
# if
# specified, is a map name to be found in the pg_ident.conf
# file.
# That table maps from ident usernames to PostgreSQL
# usernames. The
# special map name "sameuser" indicates an implied map (not
# found
# in pg_ident.conf) that maps every ident username to the
# identical
# PostgreSQL username.
#
# trust: No authentication is done. Trust that the user has the
# authority to use whatever username he specifies. Before
# PostgreSQL version 6, all authentication was done this
# way.
#
# reject: Reject the connection.
#
# password: Authentication is done by matching a password supplied
# in clear
# by the host. If AUTH_ARGUMENT is specified then the
# password
# is compared with the user's entry in that file (in the
# $PGDATA
# directory). See pg_passwd(1). If it is omitted then
# the
# password is compared with the user's entry in the
# pg_shadow
# table.
#
# crypt: Authentication is done by matching an encrypted password
# supplied
# by the host with that held for the user in the pg_shadow
# table.
#
# krb4: Kerberos V4 authentication is used.
#
# krb5: Kerberos V5 authentication is used.

# Record type "hostssl"
# ---------------------
#
# This record identifies the authentication to use when connecting to
# a
# particular database via TCP/IP sockets over SSL. Note that normal
# "host" records are also matched - "hostssl" records can be used to
# require a SSL connection.
# This keyword is only available if the server is compiled with SSL
# support
# enabled.
#
# The format of this record is identical to that of "host".

# Record type "local"
# ------------------
#
# This record identifies the authentication to use when connecting to
# a
# particular database via a local UNIX socket.
#
# Format:
#
# local DBNAME USERAUTH [AUTH_ARGUMENT]
#
# The format is the same as that of the "host" record type except that
# the
# IP_ADDRESS and ADDRESS_MASK are omitted and the "ident", "krb4" and
# "krb5"
# values of USERAUTH are not allowed.

# For backwards compatibility, PostgreSQL also accepts pre-version 6
# records,
# which look like:
#
# all 127.0.0.1 0.0.0.0

# TYPE DATABASE IP_ADDRESS MASK USERAUTH MAP

#host all 127.0.0.1 255.255.255.255 trust

# The above allows any user on the local system to connect to any
# database
# under any username.

#host template1 192.168.0.0 255.255.255.0 ident
sameuser

# The above allows any user from any host with IP address 192.168.0.x
# to
# connect to database template1 as the same username that ident on
# that host
# identifies him as (typically his Unix username).

#host all 192.168.0.1 255.255.255.255 reject
#host all 0.0.0.0 0.0.0.0 trust

# The above would allow anyone anywhere except from 192.168.0.1 to
# connect to
# any database under any username.

#host all 192.168.0.0 255.255.255.0 ident
omicron
#
# The above would allow users from 192.168.0.x hosts to connect to any
# database, but if Ident says the user is "bryanh" and he requests to
# connect as PostgreSQL user "guest1", the connection is only allowed
# if
# there is an entry for map "omicron" in pg_ident.conf that says
# "bryanh" is
# allowed to connect as "guest1".

# By default, allow anything over UNIX domain sockets and localhost.

local all trust
host all 127.0.0.1 255.255.255.255 trust
host all 207.158.72.11 255.255.255.255 trust
host all 207.158.72.45 255.255.255.255 trust
#

Thank you for your time and assistance.

Sincerely,

Robert Vogt IV
CEO
ArborHost

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 (voice) Internet: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

"Robert Vogt IV" <vogt@arborhost.com> writes:

Postgres 7.0.* doesn't work on PPC unless compiled -O0. The above is
a typical symptom of being compiled with higher optimization settings.
Unfortunately, it seems that our PPC RPMs for 7.0.2 were compiled with
the wrong -O level :-(. (Don't ask me why the RPMs ended up that way
when a clean source compilation uses -O0, but there it is.)

Why is this? We're running 7.0.2 and have not performed any of these
compile-time gymnastics...and we're not having any problems

Did you compile from source? If so I'd expect things to be fine. It's
just the PPC RPMs that are (or, shortly, were) on our FTP server that
are no good.

regards, tom lane

Mr. Lane,

Why is this? We're running 7.0.2 and have not performed any of these
compile-time gymnastics...and we're not having any problems

Did you compile from source? If so I'd expect things to be fine. It's
just the PPC RPMs that are (or, shortly, were) on our FTP server that
are no good.

Oh..ok. Yes, we did compile from source.

Right now, I still trying to figure out access permissions... Are there
any examples of people using database authentication? Can usernames
conflict between the general postgresql username pool and the by-database
authentication files?

Thank you for your assistance.

Sincerely,

Robert Vogt IV
CEO
ArborHost