[GENERAL] v8.2.12 released?
Has v8.2.12 been released? it is in the download file browser section, but not on the home page of the website thanks adam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is this a security risk?
On 17 Dec 2008, at 14:44, Albe Laurenz wrote: Adam Witney wrote: I would like to provide a limited view of my database to some users, so i thought of creating a second database (I can control access by IP address through pg_hba.conf) with some views that queried the first database using dblink. In my opinion dblink is not the right tool for that. It will require a user account on the secret database through which dblink accesses it. You'd have to restrict permissions for that user if you want to keep the thing secure. So why not access the secret database directly with that user and get rid of the added difficulty of dblink? You can rely on the permission system. Just grant the user the appropriate privileges on the necessary objects, and if you need the user to see only part of the data in a table, create a view for that. thanks for your reply, The user already has permissions within the 'secret' database, but normally they interact with it through a web interface only. I was worried that the user could get in and mess around with other things, such as the sequences which are used to populate primary keys. Also ideally I only wanted to create a read only access to certain parts of the database, I couldn't think of any other way to do it... are there any more standard ways of doing this? Yes. You grant read access with GRANT SELECT ON table/view TO user. It's no less secure than accessing a database as that user via dblink. thanks again for your email. The problem is that the user account already has SELECT/UPDATE/INSERT/ DELETE access on the views, as they need it when accessing the database through the web interface. What i wanted to do is provide a read only access to only some views (this is for a programmatic querying API). By using the second database i could restrict access to this side of it using IP address filtering in the pg_hba.conf file. thanks again adam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is this a security risk?
On 17 Dec 2008, at 07:48, Albe Laurenz wrote: Adam Witney wrote: I would like to provide a limited view of my database to some users, so i thought of creating a second database (I can control access by IP address through pg_hba.conf) with some views that queried the first database using dblink. In my opinion dblink is not the right tool for that. It will require a user account on the secret database through which dblink accesses it. You'd have to restrict permissions for that user if you want to keep the thing secure. So why not access the secret database directly with that user and get rid of the added difficulty of dblink? You can rely on the permission system. Just grant the user the appropriate privileges on the necessary objects, and if you need the user to see only part of the data in a table, create a view for that. thanks for your reply, The user already has permissions within the 'secret' database, but normally they interact with it through a web interface only. I was worried that the user could get in and mess around with other things, such as the sequences which are used to populate primary keys. Also ideally I only wanted to create a read only access to certain parts of the database, I couldn't think of any other way to do it... are there any more standard ways of doing this? thanks again adam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Is this a security risk?
I would like to provide a limited view of my database to some users,
so i thought of creating a second database (I can control access by IP
address through pg_hba.conf) with some views that queried the first
database using dblink.
The problem is that dblink requires non-superusers to provide a
password, but i would like to use the authentication from the first
database connection in the second dblink connection.
I can do this with the example below, but i was wondering is this a
really bad idea or does it create a security hole?
Example code:
CREATE DATABASE test1;
CREATE DATABASE test2;
\c test1
CREATE TABLE test (id int);
INSERT INTO test VALUES(1);
INSERT INTO test VALUES(2);
\c test2
CREATE OR REPLACE FUNCTION my_func() RETURNS SETOF record
AS $$
DECLARE
_username text;
_query text;
_row record;
old_path text;
BEGIN
old_path := pg_catalog.current_setting('search_path');
PERFORM pg_catalog.set_config('search_path', 'public, pg_temp',
true);
SELECT INTO _username session_user;
_query := 'SELECT * FROM dblink(''dbname=test1'', ''SET SESSION
AUTHORIZATION ' || _username || ';';
_query := _query || ' SELECT * FROM test'') ';
_query := _query || ' AS t1(id int);';
FOR _row IN EXECUTE _query LOOP
RETURN NEXT _row;
END LOOP;
PERFORM pg_catalog.set_config('search_path', old_path, true);
END;
$$
LANGUAGE plpgsql SECURITY DEFINER;
SELECT * FROM my_func() AS (id int);
thanks for any help
adam
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Implementing programatic access to a database using dblink?
Hi, Our database (8.2.6) runs on a separate machine from the web interface. Each user has their own username/password and row level security is handled by a set of views on top of the base tables. I'd like to provide programatic access to my database (I have been looking at Perl's DBIx::Class), but I want to control which tables/ views can be queried via this route. I thought of creating a second database with views wrapping dblink queries to the main database. The problem is that the dblink query needs to send a password even after the user has been authenticated against the first. Is there a way to get this to work or to get around this? Maybe on a more general note i wonder if those with more experience than me knows, does this approach sound like complete madness or are there better ways to approach this? thanks for any help Adam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] import content of XLS file into PostgreSQL
I would like to know what should i do to import the content (not all columns) of a XLS file into pgsql. is there something special to do ? Simplest way is to save it as a TAB delimited file and then look at the COPY command? Or if it needs more complicated processing... you could write a perl script to read the XLS file and insert the data from that adam ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Restrict access
On 2/8/07 13:35, in article [EMAIL PROTECTED], Michael Knudsen [EMAIL PROTECTED] wrote: Hi, I have created a database and imported a lot of data. I would like to share this database with other people but they should not be able to change anything. That is, they should only be allowed to use the SELECT command. If I create a user, this user can access the database and see the names of all tables. I can now GRANT this user permission to use SELECT on all tables. Apparently, this seems to solve the problem. However, I have noticed that if people access the database using my username (i.e. by typing psql foo -u and the providing my username) they get full access. I have tried to disable this by ALTER USER username WITH PASSWORD 'password' but that doesn't help. You still don't need to supply any password. I have used Google for several hours with coming even near a solution so any help is appreaciated! Take a look at the pg_hba.conf in the PGDATA directory, you probably have the connection set to 'trust'... Which won't require a password from that connection location/type. You will need to run a pg_ctl reload to make any changes take effect HTH Adam ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Mac OS X
I tried to install postgres onto my macbook via 'fink' and don't like it all that much. I decided to install from source, it's a fallback to my slackware days. But fink already created a user postgres and I can't seem to find anything to change it's configuration settings for shell, home director... Im not sure how fink adds the user, but try NetInfo Manager (In Applications/Utlilities). If the user is not there, then it might be in /etc/passwd as for other Unix OS's Also, does anyone know of a more current installation write-up for Mac other than what Apple provides? It's written around 7.4 and I'm not sure it's going to be optimal. PostgreSQL has been building out of the box on OSX since at least the later 7.4.x series, but certainly all of the 8.x series. So you should just follow the installation instructions for Unix in the INSTALL file that comes with the source. If you have any problems then post the errors back to the list and someone should be able to help... Cheers Adam ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Date style handling changes between 7.4.12 and 8.2.4
Hi,
I am upgrading from 7.4.12 to 8.2.4 and I have run into a difference in date
style handling...
In 7.4.12 this would work
bugasbase2=# create table date_test (name date);
CREATE TABLE
bugasbase2=# insert into date_test values('Wed Jul 11 10:51:14 GMT+01:00
2001');
However in 8.2.4 this happens
bugasbase_070529=# create table date_test (name date);
CREATE TABLE
bugasbase_070529=# insert into date_test values('Wed Jul 11 10:51:14
GMT+01:00 2001');
ERROR: invalid input syntax for type date: Wed Jul 11 10:51:14 GMT+01:00
2001
I assume something to do with date handling has changed?
I have found date_trunc and extract in the docs, but can't seem to get the
syntax right to get this to work... Is there a function that can convert the
string to be acceptable for 8.2.4?
Thanks in advance
adam
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Date style handling changes between 7.4.12 and 8.2.4
Excellent, thanks very much.
Will this make it into the general source tree? Or would I have to patch
this with future upgrades?
adam
On 12/6/07 16:51, Tom Lane [EMAIL PROTECTED] wrote:
Adam Witney [EMAIL PROTECTED] writes:
In 7.4.12 this would work
bugasbase2=# insert into date_test values('Wed Jul 11 10:51:14 GMT+01:00
2001');
Hmm, there's an intentional and an unintentional change here. The
unintentional one is that that field order (tz before year) doesn't work
anymore. Truth is that it only worked for rather small values of work
even in 7.4:
regression=# select 'Wed Jul 11 10:51:14 GMT+01:00 2001'::timestamptz;
timestamptz
2001-07-11 07:51:14-04
(1 row)
regression=# select 'Wed Jul 11 10:51:14 GMT-01:00 2001'::timestamptz;
ERROR: invalid input syntax for type timestamp with time zone: Wed Jul 11
10:51:14 GMT-01:00 2001
but as of 8.2 it fails for both the + and - variants. I think the
attached patch will fix it for you.
The intentional change is that a timezone in that POSIXy format
(ABBREV+-OFFSET) is now interpreted as meaning exactly the offset;
the ABBREV part is noise. This is per POSIX spec as far as I can tell,
but it's not what the code used to do. Won't affect you since GMT
is offset 0 anyway, but it's worth pointing out.
regards, tom lane
Index: src/backend/utils/adt/datetime.c
===
RCS file: /cvsroot/pgsql/src/backend/utils/adt/datetime.c,v
retrieving revision 1.174.2.1
diff -c -r1.174.2.1 datetime.c
*** src/backend/utils/adt/datetime.c 29 May 2007 04:59:13 - 1.174.2.1
--- src/backend/utils/adt/datetime.c 12 Jun 2007 15:47:21 -
***
*** 719,729
}
/***
* Already have a date? Then this might be a time zone name
! * with embedded punctuation (e.g. America/New_York) or
! * a run-together time with trailing time zone (e.g. hhmmss-zz).
* - thomas 2001-12-25
***/
! else if ((fmask DTK_DATE_M) == DTK_DATE_M || ptype != 0)
{
/* No time zone accepted? Then quit... */
if (tzp == NULL)
--- 719,735
}
/***
* Already have a date? Then this might be a time zone name
! * with embedded punctuation (e.g. America/New_York) or a
! * run-together time with trailing time zone (e.g. hhmmss-zz).
* - thomas 2001-12-25
+ *
+ * We consider it a time zone if we already have month day.
+ * This is to allow the form mmm dd hhmmss tz year, which
+ * we've historically accepted.
***/
! else if (ptype != 0 ||
!((fmask (DTK_M(MONTH) | DTK_M(DAY))) ==
! (DTK_M(MONTH) | DTK_M(DAY
{
/* No time zone accepted? Then quit... */
if (tzp == NULL)
Index: src/test/regress/expected/timestamptz.out
===
RCS file: /cvsroot/pgsql/src/test/regress/expected/timestamptz.out,v
retrieving revision 1.21.2.1
diff -c -r1.21.2.1 timestamptz.out
*** src/test/regress/expected/timestamptz.out 12 Jan 2007 23:35:04
- 1.21.2.1
--- src/test/regress/expected/timestamptz.out 12 Jun 2007 15:47:21 -
***
*** 153,158
--- 153,190
ERROR: time zone displacement out of range: Feb 16 17:32:01 -0097
INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 5097 BC');
ERROR: timestamp out of range: Feb 16 17:32:01 5097 BC
+ -- Alternate field order that we've historically supported (sort of)
+ -- with regular and POSIXy timezone specs
+ SELECT 'Wed Jul 11 10:51:14 America/New_York 2001'::timestamptz;
+ timestamptz
+ --
+ Wed Jul 11 07:51:14 2001 PDT
+ (1 row)
+
+ SELECT 'Wed Jul 11 10:51:14 GMT-4 2001'::timestamptz;
+ timestamptz
+ --
+ Tue Jul 10 23:51:14 2001 PDT
+ (1 row)
+
+ SELECT 'Wed Jul 11 10:51:14 GMT+4 2001'::timestamptz;
+ timestamptz
+ --
+ Wed Jul 11 07:51:14 2001 PDT
+ (1 row)
+
+ SELECT 'Wed Jul 11 10:51:14 PST-03:00 2001'::timestamptz;
+ timestamptz
+ --
+ Wed Jul 11 00:51:14 2001 PDT
+ (1 row)
+
+ SELECT 'Wed Jul 11 10:51:14 PST+03:00 2001'::timestamptz;
+ timestamptz
+ --
+ Wed Jul 11 06:51:14 2001 PDT
+ (1 row)
+
SELECT '' AS 64, d1 FROM TIMESTAMPTZ_TBL;
64 | d1
+-
Index: src/test/regress/sql/timestamptz.sql
===
RCS file: /cvsroot/pgsql/src/test/regress/sql/timestamptz.sql,v
retrieving revision 1.10
diff -c -r1.10 timestamptz.sql
*** src/test/regress/sql/timestamptz.sql 17 Oct 2006 21:03:21 - 1.10
--- src/test/regress/sql/timestamptz.sql 12 Jun 2007 15:47:21 -
***
*** 127,132
--- 127,140
INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 -0097');
INSERT
Re: [GENERAL] I have a questions, can you help-me ?
vagner mendes wrote: how can i do, for to install Postgresql in my Mac ? what´s steps i have do ? Thank you by your attention. (best to send these requests for help to the mailing list) There are several options for OSX, there is an Apple article here: http://developer.apple.com/internet/opensource/postgres.html or use the package here: http://www.entropy.ch/software/macosx/postgresql/ or if you are comfortable building from source, PostgreSQL compiles from source out of the box on OSX these days... details of this are in the source distribution. HTH adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] not valid character for Unicode
Martijn van Oosterhout wrote: On Fri, Jun 09, 2006 at 04:32:35PM +0100, Adam Witney wrote: The database will do it for you. Note that the client encoding affects input *and* output. So if you set it to latin1, the database will convert all strings to latin1 before sending them to you... ok, so my current database (7.4.12) is UNICODE, but from psql when i run this snip SELECT identifier from dba_data_base where bioassay_id = 1291 and identifier ilike '[EMAIL PROTECTED]'; identifier -- [EMAIL PROTECTED] (0A11) so the mu chatacter is not showing up. So im not sure if the database is converting the output? Is the character actually there? Do a length(identifier) on it to see how many characters there are. When doing an interactive session it's important that the client_encoding matches your display, otherwise you might find it dropping characters or messing up in other ways. yep it is there, when i display the data from the application (PHP) it shows the character on the web page. Also this causes errors when i dump from 7.4 and try to load into 8.1 (i've read that the UNICODE checking became more stringent in 8) so basically 8.1 won't accept this character... im just not entirely sure what to do about that? thanks again for your help adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] not valid character for Unicode
Hi, Im trying to upgrade from 7.4 - 8.1 but it is failing with Unicode errors. The offending character is the greek character mu (often used for micro). Here is an offending string [EMAIL PROTECTED] (in case it doesn't appear in the email, the mu is between the B and the G) Any ideas why this character is not valid in Unicode? thanks for any help adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] not valid character for Unicode
Martijn van Oosterhout wrote: On Fri, Jun 09, 2006 at 03:59:52PM +0100, Adam Witney wrote: Hi, Im trying to upgrade from 7.4 - 8.1 but it is failing with Unicode errors. The offending character is the greek character mu (often used for micro). Here is an offending string [EMAIL PROTECTED] (in case it doesn't appear in the email, the mu is between the B and the G) Any ideas why this character is not valid in Unicode? It's a valid unicode character, it's just you havn't encoded it in unicode. It's probably in Latin-1. In that case, you need to specify it in the client encoding... Hi Martijn, thanks for your quick response. Ok i am a bit confused by all this encoding stuff... i don't really know how to encode it in unicode? this is a text string that is extracted from a text file, i just put it in an INSERT statement. I have to replace fields with this in it with a valid string that will load into 8.1, do you know who i would do the conversion? thanks adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] not valid character for Unicode
I have to replace fields with this in it with a valid string that will load into 8.1, do you know who i would do the conversion? The database will do it for you. Note that the client encoding affects input *and* output. So if you set it to latin1, the database will convert all strings to latin1 before sending them to you... ok, so my current database (7.4.12) is UNICODE, but from psql when i run this show client_encoding; client_encoding - UNICODE SELECT identifier from dba_data_base where bioassay_id = 1291 and identifier ilike '[EMAIL PROTECTED]'; identifier -- [EMAIL PROTECTED] (0A11) so the mu chatacter is not showing up. So im not sure if the database is converting the output? (sorry, i am probably sounding very dim here!) thanks again for your help adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] not valid character for Unicode
For migration, you should pg_dump- it's not clear from your email whether you are doing that. If you typed up some sql in Windows which you want to load into postgres, you might try: set client_encoding to 'LATIN1'; at the top of your script. yes this was how i spotted the problem. If i pg_dump from 7.4 and then try to load into 8.1 these characters cause errors. This data was generated on windows though as you say -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] does this mean i have a corruption?
[EMAIL PROTECTED] wrote: On Mon June 5 2006 1:31 am, Adam Witney wrote: Tom Lane wrote: Adam Witney [EMAIL PROTECTED] writes: bugasbase2=# SELECT bioassay_id, count(*) from mba_data_base where bioassay_id = 5153 group by bioassay_id; bioassay_id | count -+--- 5153 | 2 9712 | 120 That's pretty interesting :-(. What PG version is this? Is there an index on bioassay_id, and if so is the query using it? REINDEXing the index might fix it. aha yes... this is 7.4.12 by the way. I was confused as loading last nights backup into a duplicate database made the problem go away. But as you suggested running a REINDEX on the offending index fixed the problem! That is interesting. What explains this behaviour? Should we be running REINDEX regularly? I don't know, so posting your question to the list. I meant to ask last night in fact, should i be worried as to why this occurred? thanks adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] does this mean i have a corruption?
Tom Lane wrote: Adam Witney [EMAIL PROTECTED] writes: I don't know, so posting your question to the list. I meant to ask last night in fact, should i be worried as to why this occurred? Yeah, you should, but since you hadn't given us any context about the problem I figured you only cared about a quick fix. Otherwise I would have suggested saving a physical copy of the broken index and table for analysis. The symptoms imply that some of the index entries were pointing to rows that didn't belong to them (ie, rows with a different bioassay_id value than what the index entry said). The only ways I know of to explain that involve database or system crashes ... you have any recently? Hi, yes, i was a bit worried about it, and hence went for the fix first! I don't think there have been any crashes, the uptime is showing 189 days and i don't see anything obvious in the PostgreSQL log. The data rows that were incorrectly being pointed to were only uploaded on friday and there had been zero user connections since friday evening. i do run a Vacuum Analyse and a full dump every night, i don't know if that makes a difference though. I have had problems with this table before (you probably don't remember) that required me (with your help Tom) to zero out blocks and reload some data. Anyway i will try to get a copy of the index if it happens again thanks again for your help adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] does this mean i have a corruption?
I have a table to which i bulk load datasets of about up to 20,000 rows at a time. It has a primary key, plus a dataset id (bioassay_id), however i just ran this query: bugasbase2=# SELECT bioassay_id, count(*) from mba_data_base where bioassay_id = 5153 group by bioassay_id; bioassay_id | count -+--- 5153 | 2 9712 | 120 bugasbase2=# SELECT bioassay_id, count(*) from mba_data_base where bioassay_id = 9712 group by bioassay_id; bioassay_id | count -+--- 9712 | 4624 any ideas why bioassay_id 9712 appears in the first query?? does this suggest corruption somewhere? thanks for any help adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] does this mean i have a corruption?
Tom Lane wrote: Adam Witney [EMAIL PROTECTED] writes: bugasbase2=# SELECT bioassay_id, count(*) from mba_data_base where bioassay_id = 5153 group by bioassay_id; bioassay_id | count -+--- 5153 | 2 9712 | 120 That's pretty interesting :-(. What PG version is this? Is there an index on bioassay_id, and if so is the query using it? REINDEXing the index might fix it. aha yes... this is 7.4.12 by the way. I was confused as loading last nights backup into a duplicate database made the problem go away. But as you suggested running a REINDEX on the offending index fixed the problem! thanks very much for your help Tom adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] How to find release notes
On 2/2/06 3:56 pm, Russ Brown [EMAIL PROTECTED] wrote: Hi, I'm looking at www.postgrsql.org and wondering how to find the release notes for 8.1.2. I can see the link for the 8.1.2 release, but that just links to a directory of the release tarballs. Actually, all that would be needed here would be a text file in that directory containing the release notes. My reasoning for wanting the release notes if to decide if a) I actually need to upgrade from 8.1.1 and b) if there are any special upgrade steps I need to perform. Finding release notes is one thing I've always struggled with when it comes to PostrgreSQL. I'm sure that the information it there, it just seems to be rather difficult to get to from the site homepage. Any chance it could be made a little easier? They're under the documentation, in Appendix E http://www.postgresql.org/docs/8.1/static/release.html -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] How to find release notes
On 2/2/06 4:06 pm, Russ Brown [EMAIL PROTECTED] wrote: Ah, there is it. Thanks! Just me not looking hard enough. However, I would argue that I shouldn't have to look so hard. I instinctively went for the 8.1.2 release link, and I think there should be a link to the release notes right there along with it. Just my opinion though. :) Well the Whats new in 8.1 link is on the front page, although a link to the Release Notes on that page (whatsnew) could be handy? -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Passwords when changing users - roles
Hi, I'm upgrading from 7.4.x to 8.1.x and I need to move my usernames over to the new roles. I can create the roles ok, but is there a way of transferring over the existing passwords, or do I have to create new passwords for each user/role? Thanks for any help adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Passwords when changing users - roles
On 1/2/06 3:04 pm, Bruce Momjian pgman@candle.pha.pa.us wrote: Adam Witney wrote: Hi, I'm upgrading from 7.4.x to 8.1.x and I need to move my usernames over to the new roles. I can create the roles ok, but is there a way of transferring over the existing passwords, or do I have to create new passwords for each user/role? pg_dumpall should transfer your passwords too. Ah I see, I only did a pg_dump as I was just transferring one database to the new server for testing. Thanks for the help Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Encoding errors when upgrading from 7.4 to 8.1
Hi, I am upgrading from 7.4.8 - 8.1.2 on Linux 2.6.14.3 #1 SMP I have installed 8.1.2 and created the database (with encoding 'UNICODE', as I had done in 7.4.8) and am trying to load a 7.4.8 dump file but I am getting a few errors like this: psql:bugasbase2-backup:45880: ERROR: invalid UTF-8 byte sequence detected near byte 0xb5 CONTEXT: COPY array_scheme, line 17560, column gene_identifier: [EMAIL PROTECTED] (0G11) This dump file will load error free into 7.4.8. Does anybody have any ideas why this is failing in 8.1.2? Thanks for any help Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Error on PostgreSQL mailing list web pages?
I'm not sure which list to send this to But is the majordomo address spelt correctly on the mailing list pages. Here for example: http://archives.postgresql.org/pgsql-advocacy/ It is spelt [EMAIL PROTECTED] Also on a couple of the others I have looked at... Is this right? Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Table design
Hi Sean, We use something similar to approach 1) to store our microarray data. We have a data table that has a few specific columns (signal median, bkg median etc) as these exist in all the file formats... Plus also some generic columns for the rest of the data fields. Then we have a definitions table that maps the column header from the file format to the column name in the database. It seems to work well for us. I can send you the table definitions if they are any use to you? Cheers Adam This might be a bit off-topic, but I'm curious what folks would do with this situation: I have about 6 different tab-delimited file types, all of which store similar information (microarray gene expression). However, the files come from different manufacturers, so each has slightly different fields with different meanings. However, there are a few columns that are shared. I may need to add table formats in the future (as we get more manufacturers). I can think of at least three ways to go about storing these data: 1) Create a single table that has as many columns as needed for ALL formats and make manufacturer-specific views, naming columns in the view as appropriate. Then put rules on the view for inserts, updates, etc. This is my first choice, I think, but adding a new manufacturer's format means creating a new view and possibly adding columns; some columns may NULL for large portions of the table. 2) Use postgres inheritance, but even shared columns in our data may have different names depending on the manufacturer, so there may be views involved anyway. 3) Use a fully-normalized strategy that stacks each column into one very long table--this would be my last choice. Thanks for any insight. (For replies, please try to reply to me directly as well as the list as I just get digests right now). Thanks, Sean ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] invalid page header in block 597621 of relation...error
On 26/11/05 4:48 pm, Tom Lane [EMAIL PROTECTED] wrote: Adam Witney [EMAIL PROTECTED] writes: I deleted the two datasets in mba_data_base that were affected by the empty pages, I also deleted the relevant two rows in measured_bioassay_base... But maybe it didn't do the right thing with the toast table for these two rows? Evidently the missing data in the toast table is associated with yet a different dataset. I'd suggest first looking into the toast table to see if you can confirm that the missing data corresponds to a swath of zeroed-out pages. If that's the case then it gives even more urgency to the need to find out what's going wrong with your filesystem (or possibly your disk drive, but my gut feel is that this is a kernel filesystem problem). The other thing you'd need to do is figure out which dataset you have to reload. A tedious way to do this is something like select sum(length(bigfield)) from maintable where dataset = 'xxx'; for various values of xxx until you see the error. Well I tracked down which row went wrong and deleted that dataset also, the backups worked fine and it seems to be ok now. Not really sure what caused all this, all these datasets (190 in total) went in in one batch the other day, so for some reason 3 of them got screwed up. Anyway I have upgraded the box to linux 2.6.14.3, so I will keep an eye on it and see how things go. Thanks again for your help Tom Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] invalid page header in block 597621 of relation...error
On 24/11/05 5:27 pm, Adam Witney [EMAIL PROTECTED] wrote: On 24/11/05 5:28 pm, Tom Lane [EMAIL PROTECTED] wrote: Adam Witney [EMAIL PROTECTED] writes: Does this help identifying what went wrong? At this point I think there's no question that your filesystem is dropping blocks :-(. Might want to check for available kernel updates, or contemplate changing to a different filesystem. Ok I think this problem is not entirely fixed, my backups are failing now with this: pg_dump: ERROR: unexpected chunk number 5153 (expected 21) for toast value 245334402 pg_dump: SQL command to dump the contents of table measured_bioassay_base failed: PQendcopy() failed. pg_dump: Error message from server: ERROR: unexpected chunk number 5153 (expected 21) for toast value 245334402 measured_bioassay_base is always inserted at the same time as mba_data_base (the table where I had the problem before) and it has a text field which is very large.. I guess this is where the TOAST comes in. Any ideas what is going on here? Thanks again for any help Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] invalid page header in block 597621 of relation...error
On 26/11/05 4:14 pm, Tom Lane [EMAIL PROTECTED] wrote: Adam Witney [EMAIL PROTECTED] writes: pg_dump: ERROR: unexpected chunk number 5153 (expected 21) for toast value 245334402 measured_bioassay_base is always inserted at the same time as mba_data_base (the table where I had the problem before) and it has a text field which is very large.. How large is very large ... on the order of 10Mb? If so I'd say this is the same problem as we saw in your table --- a lot of consecutive rows have gone missing. You could examine the toast table to confirm or deny this. Yes, around 10Mb is about right. I deleted the two datasets in mba_data_base that were affected by the empty pages, I also deleted the relevant two rows in measured_bioassay_base... But maybe it didn't do the right thing with the toast table for these two rows? Where would I need to look to see what the problem is? Thanks again for the help. Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] invalid page header in block 597621 of relation...error
On 23/11/05 10:20 pm, Tom Lane [EMAIL PROTECTED] wrote: Adam Witney [EMAIL PROTECTED] writes: Whats the best way to zero the bad block? Probably dd from /dev/zero, along the lines of dd bs=8k seek=597621 count=1 conv=notrunc if=/dev/zero of=relation (check this before you apply it ;-)). You probably should stop the postmaster while doing this, in case it has a cached copy of the page. Just wanted to clarify, should this not be dd bs=8k seek=7 count=1 conv=notrunc if=/dev/zero of=134401991.4 I thought that the 597621 blocks were split up over the . .1 .2 .3 .4 files? Thanks again Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] invalid page header in block 597621 of relation...error
On 24/11/05 2:48 pm, Tom Lane [EMAIL PROTECTED] wrote: Adam Witney [EMAIL PROTECTED] writes: Just wanted to clarify, should this not be dd bs=8k seek=7 count=1 conv=notrunc if=/dev/zero of=134401991.4 Looks reasonable. regards, tom lane Excellent thanks. I have run it and copied the changed file back into the right place and run vacuum: bugasbase2=# vacuum; WARNING: relation mba_data_base page 597621 is uninitialized --- fixing WARNING: relation mba_data_base page 640793 is uninitialized --- fixing WARNING: relation mba_data_base page 640794 is uninitialized --- fixing WARNING: relation mba_data_base page 640795 is uninitialized --- fixing WARNING: relation mba_data_base page 640796 is uninitialized --- fixing WARNING: relation mba_data_base page 640797 is uninitialized --- fixing WARNING: relation mba_data_base page 640798 is uninitialized --- fixing VACUUM Does this look reasonable? Or should I be looking for other problems? There are some different pages being fixed here from the ones that were shown before (597621), they all belong to a different dataset so looks like I have to reupload that one as well. Thanks again for your help Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] invalid page header in block 597621 of relation...error
On 24/11/05 3:52 pm, Tom Lane [EMAIL PROTECTED] wrote: Adam Witney [EMAIL PROTECTED] writes: bugasbase2=# vacuum; WARNING: relation mba_data_base page 597621 is uninitialized --- fixing This is the expected result of what you did. WARNING: relation mba_data_base page 640793 is uninitialized --- fixing WARNING: relation mba_data_base page 640794 is uninitialized --- fixing WARNING: relation mba_data_base page 640795 is uninitialized --- fixing WARNING: relation mba_data_base page 640796 is uninitialized --- fixing WARNING: relation mba_data_base page 640797 is uninitialized --- fixing WARNING: relation mba_data_base page 640798 is uninitialized --- fixing That's a bit odd. There are scenarios where all-zero pages can legitimately appear in a PG file --- specifically, if PG extends the table and the OS completes that task, but then there's a crash before PG gets to write any data into the new page. Conceivably a crash during a bulk data load process could result in half a dozen such pages together, but it seems improbable. Try looking at the data on the preceding and following pages --- does it look like there's something missing? If you mean by that, this: select * from mba_data_base where ctid = '(640792,12)'; select * from mba_data_base where ctid = '(640799,1)'; Then the data looks normal... Of course everything in between that is now blank. Is that what you meant? Thanks Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] invalid page header in block 597621 of relation...error
On 24/11/05 4:19 pm, Tom Lane [EMAIL PROTECTED] wrote: Adam Witney [EMAIL PROTECTED] writes: If you mean by that, this: select * from mba_data_base where ctid = '(640792,12)'; select * from mba_data_base where ctid = '(640799,1)'; Then the data looks normal... Of course everything in between that is now blank. The question is, can you tell whether any data is actually missing? In the crash scenario I was describing, no committed data would be lost. If these blocks went zero because of filesystem misfeasance, however, you might have lost data ... Well each of these datasets are about 20,000 rows each... So I can tell which one is in (640792,12) and in (640799,1), they have the same dataset id value. Im assuming the missing ones in the middle will therefore be from the same dataset as well then... So I know which files did not get uploaded properly and I can re-upload them Is that what you were referring to? Thanks again Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] invalid page header in block 597621 of relation...error
On 24/11/05 4:42 pm, Tom Lane [EMAIL PROTECTED] wrote: Adam Witney [EMAIL PROTECTED] writes: On 24/11/05 4:19 pm, Tom Lane [EMAIL PROTECTED] wrote: The question is, can you tell whether any data is actually missing? Well each of these datasets are about 20,000 rows each... So I can tell which one is in (640792,12) and in (640799,1), they have the same dataset id value. Im assuming the missing ones in the middle will therefore be from the same dataset as well then... So I know which files did not get uploaded properly and I can re-upload them But can you tell whether there *were* any in the middle? It might be worth counting the rows currently present for that dataset, then reloading and counting again. Ah yes I see what you mean. I have already done a count(*) on them: bugasbase2=# select count(*) from mba_data_base where bioassay_id in (5176); count --- 2 (1 row) bugasbase2=# select count(*) from mba_data_base where bioassay_id in (5177); count --- 19988 (1 row) bugasbase2=# select count(*) from mba_data_base where bioassay_id in (5203); count --- 19928 And the two affected datasets (5177, 5203) are short on rows compared to a correct one (5176) Does this help identifying what went wrong? Thanks Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] invalid page header in block 597621 of relation...error
On 24/11/05 5:28 pm, Tom Lane [EMAIL PROTECTED] wrote: Adam Witney [EMAIL PROTECTED] writes: Does this help identifying what went wrong? At this point I think there's no question that your filesystem is dropping blocks :-(. Might want to check for available kernel updates, or contemplate changing to a different filesystem. Well I have been considering moving up to the 2.6.x kernels, this has made my other machines more reliable (they are not db machines though) Thanks again for all your help adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] invalid page header in block 597621 of relation... error
Hi, I just had this error in my database: bugasbase2=# SELECT count(*) from mba_data_base; ERROR: invalid page header in block 597621 of relation mba_data_base Any ideas whats going on? Am a bit worried as this is my production database. Thanks for any assistance Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] invalid page header in block 597621 of relation...error
On 23/11/05 8:55 pm, Tom Lane [EMAIL PROTECTED] wrote: Adam Witney [EMAIL PROTECTED] writes: bugasbase2=# SELECT count(*) from mba_data_base; ERROR: invalid page header in block 597621 of relation mba_data_base Sounds like a data corruption problem :-(. Do you want to pull out that page and see what's in it? Something like dd bs=8k skip=7 count=1 if=relationfile | od -x where you need to use oid2name or something similar to determine which file contains that relation, and then append .4 because block 597621 would be in the fifth segment of the file. (I got 7 from select 597621 % 131072) Thanks for the help Here is the output: [EMAIL PROTECTED]:/opt$ dd bs=8k skip=7 count=1 if=134401991.4 | od -x 000 * 1+0 records in 1+0 records out 001 1d9e 201c 0fa0 0010 000b 0010020 0ca6 19fb 1797 0ab4 000a 0001 0010040 01af 000a 0001 0ca7 0010060 0012 0010 0002 1190 068f 0c9a 0010100 0391 0012 000f 0002 10aa 0010120 0971 06f3 184c 0010 0010 0010140 0d14 0f78 1c5c 066d 0012 000f 0010160 0009 0ff5 26c1 077a 0c8a 0012 0010200 000f 0009 0ff5 26c1 077a 0c8a 0010220 0008 0002 0010240 0010260 0001 0010300 0008 7059 3376 0004 0010320 1d53 003b 0021 0009 1e75 0010340 0006 004d 0813 ff28 fedf fffb ff87 1803 0010360 221c 0ea7 e6dc 009c 1439 0010400 0017 4353 522d 7461 6f69 305f 2036 0010420 3228 4537 3031 0029 0012 000e 0010440 0056 0a6a 1a0a 1a0a 1a2c 0012 0010460 000e 004e 1b26 0dda 03e0 238c 0010500 000a 0001 004c 000a 0010520 0001 004a 0012 000e 0010540 002c 2208 12b6 2232 0ce4 0012 0010560 000f 0027 2448 0aad 0001 1dec 0010600 0019 000b 0007 0001 0010620 000c 0001 0199 1388 000c 0010640 0001 0ca7 1388 0012 000e 0010660 0073 16c9 0d05 0d05 0ce4 0012 0010700 000e 0047 2404 1e70 081d 23f0 0010720 000a 0001 003c 000a 0010740 0001 011a 000a 0001 0010760 1438 000c 0001 0001 0002 0890 0011000 000a 0002 1d4c 0008 0011020 0001 000a 0001 003c 0011040 0010 0010 2591 04b3 1a9d 07e3 0011060 0008 0001 000a 0001 0011100 0009 0012 0010 0001 22c2 0011120 131e 07da 21f5 0012 4010 0011140 0001 1c60 234b 2081 0363 0010 0011160 000f 1e06 0395 1980 164e 0012 0011200 000d 019b 08d8 234b 2081 03e8 0011220 0012 000d 0ca6 1c91 237a 0d8f 0011240 0fa0 000c 0001 0199 1388 0011260 000c 0001 0ca7 1388 0012 0011300 0010 0001 22c2 131e 07da 21f5 0011320 0012 4010 0001 1c60 234b 2081 0011340 0363 0010 000f 1e06 0395 0011360 1980 164e 0012 000f 0008 2032 0011400 0390 1032 0b5e 0012 000f 0011420 0008 2032 0390 1032 0b5e 0008 0011440 0002 0011460 0011500 0001 0011520 0008 7059 3376 0004 1d53 003b 0011540 0021 0009 1e75 0005 004d 0011560 0813 ff28 fedf fffb ff87 1803 0011600 221b 0ea7 e6db 009c 1439 0017 0011620 4353 522d 7461 6f69 305f 2035 3228 4137 0011640 3031 0029 0012 000e 005b 229f 0011660 18db 18db 1900 0012 000e 0011700 004f 07c6 01f1 1404 0ed8 000a 0011720 0001 0057 000a 0001 0011740 0045 0012 000d 0033 1608 0011760 1186 1eec 0fa0 0012 000e 0012000 002a 113e 066c 136d 2134 0019 0012020 000a 0007 0001 000c 0012040 0001 018c 1388 000c 0001 0012060 0ca7 1388 000c 0001 0073 1388 0012100 0012 000f 0032 02a5 25bd 00a9 0012120 1356 000a 0001 0058 0012140 000a 0001 00c9 000a 0012160 0001 1f96 000c 0001 0001 0012200 0001 171f 0010 0010 1eb1 0594 0012220 1652 0b29 0008 0001 000a 0012240 0001 0058 000a 0001 0012260 0001 0008 0001 000a 0012300 0001 000b 0012 000f 0012320 0002 0207 1d03 15de 18a6 0012 0012340 0010 0001 2436 1511 1a71 1510 0012360 0010 0010 1b8a 0a20 0cd1 249b 0012400 0012 000e 018a 1661 11fe 0c9e 0012420 11f8 0010 000c 0ca6 1f0d 0012440 1cef 1a3e 000c 0001 018c 1388 0012460 000c 0001 0ca7 1388 0012 0012500 000f 0002 0207 1d03 15de 18a6 0012520 0012 0010 0001 2436 1511 1a71 0012540 1510 0010 0010 1b8a
Re: [GENERAL] invalid page header in block 597621 of relation...error
On 23/11/05 9:36 pm, Tom Lane [EMAIL PROTECTED] wrote: Adam Witney [EMAIL PROTECTED] writes: Thanks for the help Here is the output: [EMAIL PROTECTED]:/opt$ dd bs=8k skip=7 count=1 if=134401991.4 | od -x 000 * 001 1d9e 201c 0fa0 0010 000b 0010020 0ca6 19fb 1797 0ab4 000a 0001 0010040 01af 000a 0001 0ca7 0010060 0012 0010 0002 1190 068f 0c9a ... Unfortunately I have no idea what any of that means! The second half of the page looks reasonable, but the first half is all zeroes :-(. (dd uses * to mean same as above.) It's unlikely that this is Postgres' fault; I can't think of any plausible pathology within PG that would so carefully zero out just half of a page. What seems more likely is that the block size on the underlying filesystem is 4K, and that either a kernel bug or a disk drive error has caused the system to drop the contents of one block. If I had to bet with no additional info, I'd bet on kernel bug. What's the platform exactly, and what filesystem are you using? Linux bugsdb 2.4.26 #1 SMP Wed May 5 12:08:48 BST 2004 i686 unknown /dev/md2 on /pg_data type xfs (rw,noatime) /dev/md2 is a software RAID5 device. Also PostgreSQL 7.4.8 on i686-pc-linux-gnu, compiled by GCC 2.95.4 This table is only ever COPY'd to from data files, no updates or deletes, if I could find out which data file this bit comes from I could just reupload that file... Is it possible to tell what the data actually is from the data I sent? Thanks again Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] invalid page header in block 597621 of relation...error
On 23/11/05 9:55 pm, Tom Lane [EMAIL PROTECTED] wrote: Adam Witney [EMAIL PROTECTED] writes: This table is only ever COPY'd to from data files, no updates or deletes, if I could find out which data file this bit comes from I could just reupload that file... Is it possible to tell what the data actually is from the data I sent? You might try dumping the page with od -c instead of -x and looking to see if there are any recognizable strings. Also try the same on the preceding or following pages. Or try dumping what's on the preceding and following pages, eg select * from tab where ctid = '(597620,1)'; select * from tab where ctid = '(597620,2)'; ... (run the item number up high enough to be sure you've seen all the live rows on the page). To get the file back in a usable state before you add more data, you'll want to zero the bad block out completely, and then do a VACUUM to see if there are any other damaged pages. Aha, the select statements above made it easy to identify the correct file. Whats the best way to zero the bad block? Could I not just delete that data and reload it? Thanks again Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Access management for DB project.
On 8/9/05 11:08 am, Bohdan Linda [EMAIL PROTECTED] wrote: Hi, I started thinking of some security access management. Basically imagine this scenario according users: 1) Writer does only inserts to black hole. 2) Reader does only reports on inserted data, cannot modify or add anything 3) Maintainer can run a task on the data, but cannot read or add anything. The task has to have read/write access to the tables. The first 2 types are easily solvable, but with the third type I have problem. I have created task in plpgsql, I granted permissions to an user to execute the task, but revoked on him all rights to tables. Logically task failed. You could create the function with SECURITY DEFINER, that way the function will have the permissions of the user that creates it as opposed to the user that runs it CREATE my_func(int) RETURNS int SECURITY DEFINER AS '. -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] http://www.postgresql.org/docs/8.0/static/xfunc-sql.html
On 22/8/05 10:19 am, Nigel Horne [EMAIL PROTECTED] wrote: On Fri, 2005-08-19 at 17:29, Tom Lane wrote: Adam Witney [EMAIL PROTECTED] writes: Ah you want to return a record I suppose? CREATE TABLE test (id int, name text); INSERT INTO test VALUES(1, 'me'); INSERT INTO test VALUES(2, 'you'); CREATE FUNCTION test_func() RETURNS SETOF record AS ' SELECT id, name FROM test; ' LANGUAGE SQL; Or better, RETURNS SETOF test, so you don't have to describe the output record type every time you call it. Seeing as there will only ever be one row in this table, do I need the SETOF field? Try it and see I think you don't need it for a single row. adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL]
On 22/8/05 2:56 pm, Nigel Horne [EMAIL PROTECTED] wrote: On Mon, 2005-08-22 at 14:49, Tino Wildenhain wrote: Nigel Horne schrieb: On Fri, 2005-08-19 at 16:34, A. Kretschmer wrote: am 19.08.2005, um 15:58:20 +0100 mailte Nigel Horne folgendes: I can't work out from that how to return more than one value. 17:35 rtfm_please For information about srf 17:35 rtfm_please see http://techdocs.postgresql.org/guides/SetReturningFunctions 17:35 rtfm_please or http://www.postgresql.org/docs/current/static/xfunc-sql.html#AEN28835 How does that help with my problem? I seems to discuss returning more than one row of a table which is not the question I asked. try to tell your questions more precisely :-) I want to return more than one value from a procedure, e.g. a string and an integer. I think you want to return a record or tabletype. Not really, since those values could be computed on the fly, they may not be values in a database. IIrc you got the answers to that already :-) Nope. Hi Nigel, Well if you have not yet received the answer that you require, then you probably haven't asked your question properly Because several people have answered the question as you stated it. You might have to be more specific about your requirements to get a proper answer. Cheers Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] How to cancel a query if SIGINT does not work?
Hi, I have a query that appears to have hung somehow. I have tried sending a SIGINT but this does not cancel it... What's the next step without taking down the whole server? Thanks Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] http://www.postgresql.org/docs/8.0/static/xfunc-sql.html
I can't work out from that how to return more than one value. Hi Nigel, Add SETOF to your function like so: CREATE TABLE test (id int); INSERT INTO test VALUES(1); INSERT INTO test VALUES(2); CREATE FUNCTION test_func() RETURNS SETOF integer AS ' SELECT id FROM test; ' LANGUAGE SQL; SELECT test_func(); Cheers Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] http://www.postgresql.org/docs/8.0/static/xfunc-sql.html
On 19/8/05 4:38 pm, Nigel Horne [EMAIL PROTECTED] wrote: On Fri, 2005-08-19 at 16:30, Adam Witney wrote: I can't work out from that how to return more than one value. Hi Nigel, Add SETOF to your function like so: CREATE TABLE test (id int); INSERT INTO test VALUES(1); INSERT INTO test VALUES(2); CREATE FUNCTION test_func() RETURNS SETOF integer AS ' SELECT id FROM test; ' LANGUAGE SQL; What if one value I want to return is an integer, and another one is a string? Ah you want to return a record I suppose? CREATE TABLE test (id int, name text); INSERT INTO test VALUES(1, 'me'); INSERT INTO test VALUES(2, 'you'); CREATE FUNCTION test_func() RETURNS SETOF record AS ' SELECT id, name FROM test; ' LANGUAGE SQL; SELECT * FROM test_func() AS (id int, name text); id | name +-- 1 | me 2 | you Cheers Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] How to cancel a query if SIGINT does not work?
On 19/8/05 5:27 pm, Tom Lane [EMAIL PROTECTED] wrote: Adam Witney [EMAIL PROTECTED] writes: I have a query that appears to have hung somehow. I have tried sending a SIGINT but this does not cancel it... What's the next step without taking down the whole server? In theory that should always work. What PG version is this? Can you attach to the stuck backend with gdb and get a stack trace? select version(); version --- PostgreSQL 7.4.8 on i686-pc-linux-gnu, compiled by GCC 2.95.4 Am not particularly familiar with gdb, but if you tell me the steps I will do it and report back Thanks for your help Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] How to cancel a query if SIGINT does not work?
On 19/8/05 6:08 pm, Tom Lane [EMAIL PROTECTED] wrote: Adam Witney [EMAIL PROTECTED] writes: On 19/8/05 5:27 pm, Tom Lane [EMAIL PROTECTED] wrote: In theory that should always work. What PG version is this? Can you attach to the stuck backend with gdb and get a stack trace? Am not particularly familiar with gdb, but if you tell me the steps I will do it and report back Think I've posted this before, but: Determine PID of backend you are interested in, eg with ps As postgres user, do $ gdb /path/to/postgres-executable PID-of-process gdb prints a bunch of junk gdb bt ... copy and paste the output given here ... gdb quit gdb: ok to detach? y $ If you get a pile of purely numeric output from bt, it's useless (means you're running symbol-stripped executables). I'm hoping for at least some function names. (gdb) bt #0 0x40193812 in recv () from /lib/libc.so.6 #1 0x081044d8 in secure_read () #2 0x081084c7 in pq_recvbuf () #3 0x0810853d in pq_getbyte () #4 0x080cb12b in CopyGetData () #5 0x080cb36e in CopyGetChar () #6 0x080cd9d7 in CopyReadLine () #7 0x080cce90 in CopyFrom () #8 0x080cbdea in DoCopy () #9 0x08157759 in ProcessUtility () #10 0x08156a1b in PortalRunUtility () #11 0x08156ae2 in PortalRunMulti () #12 0x0815667e in PortalRun () #13 0x08153843 in exec_simple_query () #14 0x08155ab9 in PostgresMain () #15 0x0813523d in BackendFork () #16 0x08134ad5 in BackendStartup () #17 0x08133439 in ServerLoop () #18 0x08132fa5 in PostmasterMain () #19 0x081097af in main () #20 0x400dc14f in __libc_start_main () from /lib/libc.so.6 Also, what's the best way to cancel the query now? Thanks again for your help adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] How to cancel a query if SIGINT does not work?
On 19/8/05 6:20 pm, Martijn van Oosterhout kleptog@svana.org wrote: On Fri, Aug 19, 2005 at 06:10:28PM +0100, Adam Witney wrote: If you get a pile of purely numeric output from bt, it's useless (means you're running symbol-stripped executables). I'm hoping for at least some function names. [trace below] That's not stuck, somebody has typed COPY FROM STDIN and not sent any data (or not finished it). Should be able to Ctrl-C it though. Are you sure you have the right backend? Closing the frontend attached to it should do... Ah Its a web application... I assumed that because the browser had been closed that the query must have hung. But just restarted the apache server and that cancelled it, so apache must have kept the query running despite the user closing the browser. Sorry I probably should have thought of that! Thanks for your help Tom and Martijn Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] current_user inside SECURITY DEFINER function?
I think you want to use session_user instead Adam Is there any way to get the name of the current user inside a PL/pgSQL function that is defined with security definer? current_user gives the name of the user who created the function. The reason I want this is that I intend to use functions to maintain security, like this: userA has only select permission on myTable. The only way userA can insert to myTable is by providing parameters for and executing myFunction. myFunction was created by user postgres who does have insert permission on myTable. However the code inside myFunction needs to do different things, depending on who called it. Is there any way of getting the user? regards Richard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Copying data from int column to array column
Hi, I am trying to copy the data from an integer column into an array column in the same table. Something like this CREATE TABLE test (field1 INT, field2 INT, field3 INT[]); INSERT INTO test VALUES(1); INSERT INTO test VALUES(2); INSERT INTO test VALUES(3); INSERT INTO test VALUES(4); INSERT INTO test VALUES(5); UPDATE test SET field2 = field1; UPDATE test SET field3[1] = field1; Why does the UPDATE of field2 work, but the UPDATE of field3 does not? Thanks for any help Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Large Object = invalid input syntax for integer:
On 6/6/05 4:58 am, grupos [EMAIL PROTECTED] wrote:
I need to use large objects BUT I am having problemns... I instaled
PostgreSQL 8.0.3 windows version with lo module.
first, I created the table below:
CREATE TABLE test
(
description varchar(20),
picture lo
)
WITHOUT OIDS;
After trying to make one insert without value for the lo I get the error
below:
INSERT INTO test VALUES (1);
ERROR: invalid input syntax for integer:
Which value I can put on the default of the lo to solve this ? I alreday
tryed -1, 0, 1, null but nothing works...
Why this problem?
I think the problem is nothing to do with lo, you are trying to insert an
integer into a varchar field, try
INSERT INTO test VALUES ('1');
HTH
adam
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Issue with OS X
You will need to modify your shared memory settings or change your shared_buffers parameter in the postgresql.conf file. (Most likely need to increase SHMMAX as the default for OSX is quite low as I recall) Take a look here: http://www.postgresql.org/docs/8.0/static/kernel-resources.html There is a section on MacOSX about half way down the page HTH Adam Hi, I am trying to install postgresql 8 on a G5 OS X machine and cannot get past this error i have tried changing the shared buffer with no luck. Error message: /usr/local/pgsql/data postgres$ FATAL: could not create shared memory segment: Invalid argument DETAIL: Failed system call was shmget(key=5432001, size=10338304, 03600). HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter. You can either reduce the request size or reconfigure the kernel with larger SHMMAX. To reduce the request size (currently 10338304 bytes), reduce PostgreSQL's shared_buffers parameter (currently 1000) and/or its max_connections parameter (currently 100). If the request size is already small, it's possible that it is less than your kernel's SHMMIN parameter, in which case raising the request size or reconfiguring SHMMIN is called for. The PostgreSQL documentation contains more information about shared memory configuration. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Distinguishing between connections in pg_hba.conf
On 17/5/05 2:59 pm, Scott Marlowe [EMAIL PROTECTED] wrote: On Tue, 2005-05-17 at 05:08, Stephane Bortzmeyer wrote: On Mon, May 16, 2005 at 03:31:27PM -0500, Scott Marlowe [EMAIL PROTECTED] wrote a message of 48 lines which said: but how do you assign it so that requests from apache appear on the db box as one IP address, and requests coming through stunnel appear as the second IP address? That's kinda OS dependent. On RedHat you should have some kind of netconfig command I do not think it was the question. For stunnel, the solution is probably : -I host IP of the outgoing interface is used as source for remote connections. Use this option to bind a static local IP address, instead. Sorry, I'm not that familiar with stunnel, so I didn't really get it that that's what the OP was asking... Hi, Yep I missed the -I switch in the stunnel docs. And using Scott's idea of an network interface alias, apache connects with one IP and stunnel connects with another! Thanks again for your help Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Distinguishing between connections in pg_hba.conf
Hi, I have a web application (PHP) which runs on its own box, and connects to a database on a second box. The database box is behind the firewall and only accepts connections from the web server. I have set up stunnel on the web server and I would like to allow some limited external direct access to the db server, but I would like connections from stunnel to only access a specific database. The problem is that both the web server and the stunnel connections will come from the same box, and hence the same IP address, is there anyway I can distinguish between these two connection methods in pg_hba.conf? (I can't do it on username either) Thanks for any advice Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Distinguishing between connections in pg_hba.conf
On 16/5/05 8:17 pm, Scott Marlowe [EMAIL PROTECTED] wrote: On Mon, 2005-05-16 at 07:35, Adam Witney wrote: Hi, I have a web application (PHP) which runs on its own box, and connects to a database on a second box. The database box is behind the firewall and only accepts connections from the web server. I have set up stunnel on the web server and I would like to allow some limited external direct access to the db server, but I would like connections from stunnel to only access a specific database. The problem is that both the web server and the stunnel connections will come from the same box, and hence the same IP address, is there anyway I can distinguish between these two connection methods in pg_hba.conf? (I can't do it on username either) Add an alias to each machine's ethernet card, along with a name. So, if you've got 10.1.1.1 as the IP on the web server and 10.2.1.1 on the db server, add 10.1.1.2 and 10.2.1.2 on each respectively, and give them some similar name, like web02 and db02 if their names are web01 and db01. Set up routes to use the other IP addresses with those names and you should be able to do it. I haven't fleshed it out step by step, but you get the basic idea, right? Hi, Thanks for your reply. So I see how you add an extra IP address to the web server box, but how do you assign it so that requests from apache appear on the db box as one IP address, and requests coming through stunnel appear as the second IP address? Thanks again Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Backup strategy
Hi, I am setting up the backup strategy for my database. The database contains around 25 tables containing quite a lot of data that does not change very much (and when it does it is changed by me). And around 20 tables containing data which will be created and updated by the users regularly. I would like to backup the two sets of data separately at different intervals. The first set only when I update it and the second set several times per day. Would it be best to set up a separate schema for the static tables? If so what would be the simplest and safest way to transfer these tables into a new schema? Thanks for any help Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Backup strategy
On 18/1/05 8:38 pm, Lonni J Friedman [EMAIL PROTECTED] wrote: On Tue, 18 Jan 2005 18:23:23 +, Adam Witney [EMAIL PROTECTED] wrote: Hi, I am setting up the backup strategy for my database. The database contains around 25 tables containing quite a lot of data that does not change very much (and when it does it is changed by me). And around 20 tables containing data which will be created and updated by the users regularly. I would like to backup the two sets of data separately at different intervals. The first set only when I update it and the second set several times per day. Would it be best to set up a separate schema for the static tables? If so what would be the simplest and safest way to transfer these tables into a new schema? Thanks for any help pg_dump allows you to backup individual tables. Once you do that, you could import them into a new database. Would this take care of sequences and any other associated objects also? -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Books for experienced DB developer
It has been able to do this for some time now... Take a look 33.4.4. SQL Functions Returning Sets http://www.postgresql.org/docs/7.4/static/xfunc-sql.html I'm afraid this is still a problem. From my knowledge, Postgres function is able to return a single result-set not multiple. I may have missed some facility... Regards, Patrick --- Patrick Fiche email : [EMAIL PROTECTED] tél : 01 69 29 36 18 --- -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Tino Wildenhain Sent: vendredi 7 janvier 2005 11:45 To: Patrick FICHE Cc: Craig Bryden; pgsql-general@postgresql.org Subject: Re: [GENERAL] Books for experienced DB developer On Fri, 2005-01-07 at 11:25 +0100, Patrick FICHE wrote: Hi Craig, 2 years ago, I had to do some porting from MS SQL to Postgres. All the application logic was coded in stored procedures... The major problem I was faced to, was to port procedures returning multiple result-sets... At least, this isnt a problem anymore :-) Regards Tino ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Pattern matching a line ending character
Hi, I think I have managed to get a line ending character in some of my text fields. If I do a select I get this: array_design_id | gene_identifier | control_name -+---+--- 10 | SC-Calibration_10 (13F24) | SC-Calibration_10 10 | SC-Calibration_10 (13E24) | SC-Calibration_10 8 | SC-Calibration_10 (9H17) | SC-Calibration_10 8 | SC-Calibration_10 (9G17) | SC-Calibration_10 So I think in some cases the gene_identifier has a line ending character, I am trying to select on it, but cannot seem to find the right syntax: select * from array_scheme where gene_identifier like '%\\r'; And have tried various combinations of \\r\\f, \\f\\r, \\f, \\n etc Anybody have any ideas of what I may be doing wrong... Or what maybe going on here? Thanks for any help Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Pattern matching a line ending character... Please ignore previous message, solved!
As usual jumped the gun on my message, solved it... Its '%\r' Thanks adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Substring question
I am trying to select a part of a text field based on a regular expression, the data looks like this Rv0001c_f Rv0002_r Rv1003c_r Etc I would like to be able to select like this (this is a regular expression I would do in perl) SELECT substring(primer_name, '(\w+)\d\d\d\d[c]*_[fr]$') from primer; Is it possible to do this in SQL? Thanks for any help adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Inserting greek letters
Hi, I have a database fronted with PHP. I want the user to be able to put scientific notation characters (greek letters really) and store them in the database. Do I have to have created the database with UNICODE encoding to do this? Also, is there anything in the PHP I have to adjust to be able to store the data and display the data? Thanks for any help Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Inserting greek letters
Hi Martijn, thanks for your quick reply... As for PHP, you need to have the browser and PHP agree on what character set they're going to use. Then you set the client encoding appropriately and PostgreSQL will make sure you get the information you expect. Im not sure, where do I set the client encoding? Thanks adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] table name in pl/pgsql
I think you would have to do it something like this, although whether the SELECT INTO works in an EXECUTE context I am not sure (note, completely untested code!) CREATE FUNCTION get_count(text, text) RETURNS int2 AS ' DECLARE cnt int4; BEGIN EXECUTE ''SELECT INTO cnt COUNT(*) FROM table_'' || $1 || '' WHERE key = '' || $2; RETURN cnt; END;' LANGUAGE 'plpgsql'; New question: i have tables like table_20041124, table_20041125, etc... i'm trying to make function (for example): = CREATE FUNCTION get_count(text, text) RETURNS int2 AS ' DECLARE cnt int4; BEGIN SELECT INTO cnt COUNT(*) FROM table_$1 -- That doesn't work WHERE key = $2; RETURN cnt; END;' LANGUAGE 'plpgsql'; = call this function by: = SELECT get_count(20041124, something); = string in funstion - FROM table_$1 how could i get a final correct table name here? Thanx! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Can COPY skip a header line?
On 19/11/04 6:46 pm, Pierre-Frédéric Caillaud [EMAIL PROTECTED] wrote: can do this fine with small files But if I get above a 1000 rows it takes so long it time out. PHP is slow, but not *that* slow, you have a problem somewhere ! Aha yes, I was reading the file doing this fgets($fh, 1048576))) ... But changing it to fgets($fh, 1024))) Which is a large enough amount of data for my needs improved the speed dramatically! Thanks to all those that replied adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Can COPY skip a header line?
Following on from my question yesterday... Can COPY then be made to skip a header line (the first line of the file say)? The problem is this... I need to allow a user to upload a data file through a web browser (PHP driven). This is then processed and the selected file columns mapped to fields in a database, and then the data file uploaded. I can do this fine with small files But if I get above a 1000 rows it takes so long it time out. I can upload a 10,000 row equivalent file using COPY from psql in 2 seconds, so the time is down to the PHP processing (really all it does is send itto pg_put_Line) I liked Toms idea to create a temporary table, but I need to be able to get rid of the header row then... Is there anyway of avoiding getting PHP involved in the file processing? Any ideas? Thanks Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Can COPY skip a header line?
Hi Martijn, Can I get PHP to remove the first row without reading the whole file in? If there was a way where PHP would just chop the first row off that would be ideal... Thanks adam Wouldn't it be easier to have PHP remove the first row? And PHP has to be there because Apache is sending all the data to it. Hope this helps, On Fri, Nov 19, 2004 at 03:43:18PM +, Adam Witney wrote: Following on from my question yesterday... Can COPY then be made to skip a header line (the first line of the file say)? The problem is this... I need to allow a user to upload a data file through a web browser (PHP driven). This is then processed and the selected file columns mapped to fields in a database, and then the data file uploaded. I can do this fine with small files But if I get above a 1000 rows it takes so long it time out. I can upload a 10,000 row equivalent file using COPY from psql in 2 seconds, so the time is down to the PHP processing (really all it does is send itto pg_put_Line) I liked Toms idea to create a temporary table, but I need to be able to get rid of the header row then... Is there anyway of avoiding getting PHP involved in the file processing? Any ideas? Thanks Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Can COPY skip columns?
Hi, Is it possible for the COPY command to read data from a file, but skip specific columns? Thanks Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Can COPY skip columns?
Hi Joshua,
Sorry, I meant skip a column in the file, not the database table, or is that
what you meant?
Thanks
adam
Adam Witney wrote:
Hi,
Is it possible for the COPY command to read data from a file, but skip
specific columns?
\h copy
COPY tablename [ ( column [, ...] ) ]
TO { 'filename' | STDOUT }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ CSV [ QUOTE [ AS ] 'quote' ]
[ ESCAPE [ AS ] 'escape' ]
[ FORCE QUOTE column [, ...] ]
Yes.. you just have to specify the columns...
Sincerely,
Joshua D. Drake
Thanks
Adam
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Can COPY skip columns?
On 18/11/04 5:15 pm, Tom Lane [EMAIL PROTECTED] wrote: Adam Witney [EMAIL PROTECTED] writes: Is it possible for the COPY command to read data from a file, but skip specific columns? Nope. When you get into significant massaging of the input data, usually the best bet is to COPY into a temp table that exactly matches the format of the data file, and then do your rearrangements using an INSERT/SELECT into the final target table. Ah ok. Can COPY be made to skip the first row then? To avoid the column headers? Thanks adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Wanted: Want to hide data by using PL/PGSQL functions
To return a result set use SETOF, like so CREATE FUNCTION test() RETURNS SETOF text AS ' To allow access to the tables only through a function, take a look at declaring your functions with SECURITY DEFINER CREATE FUNCTION test() RETURNS SETOF text SECURITY DEFINER AS ' .. Inside the function you will now have permissions of the user that created the function. See here for details http://www.postgresql.org/docs/7.4/interactive/sql-createfunction.html Also this may be useful http://techdocs.postgresql.org/guides/SetReturningFunctions HTH adam Hello, I have following problem: A user xy shouldn't have any rights to a table, but needs data from the content of the table. My idea was to setup a PL/PGSQL procedure to fetch the data from the table, so that the user only is allowed to access the procedure. I also tried using a SQL function, but this doesn't work, too. Working with views may be a solution - or are e.g. cursors in pl/pgsql the solution ?? The problem i run into is, that although i can read the data and return it, I can not return more than one row each function call. Is it possible to return a whole resultset ? My last try was: drop function test(int); create function test(int) returns table_name as ' select * from table_name where column_name1= $1 order by column_name1; ' language sql; select * from test(1) ; The pl/pgsql variant: drop function test(); CREATE FUNCTION test() RETURNS text AS ' declare target table_name%ROWTYPE; begin select * into target from table_name ; return target.column_name1 || target.column_name2; end; ' LANGUAGE plpgsql; select test(); But in PL/pgsql i am not able to return a cursor or something like this and I am not able to return more than one row. So i have got 2 maybe solutions, but none of them works. Has anyone a hint, how to hide original tables and making their data selectable to some users ? The result really should be a select a.* , b.* from a,b where a.state!=imgonewild Thanks in advance, Andre ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] DML Restriction unless through a function
On 30/6/04 2:52 pm, Bruno Wolff III [EMAIL PROTECTED] wrote: On Wed, Jun 30, 2004 at 13:21:00 +0100, [EMAIL PROTECTED] wrote: If I revoke insert,update,delete privileges on a table, would the user still be able to affect data manipulation through a function? Is that another way of getting around this? If you use a security definer function where the function creator has access to the table, but others don't then you can do that. There are some issues if you start using such functions in views, but for simple usage it will do what you want. Out of interest, what are the issues? Thanks adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Web DB Management tool
There is phpPgAdmin which works very nicely http://phppgadmin.sourceforge.net/ But there are others, take a look under Administration Tools here http://techdocs.postgresql.org/oresources.php I am looking for something like 'phpmyadmin' for postgre, so I can admin all my DB's through a web site. Anyone had any good experiences? Or any really good just plain applications that do that? Dan Baughman -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Listing databases
If you attach with psql then just use \l Or this works as well SELECT datname from pg_database; Sorry for the newbie question, but how do you get PostgreSQL to list the available databases? I know how to log into a certain database, but not list all of them. I know this must be possible because the phppgAdmin web site demonstrates it with their trial server. Thanks, Doug Hall ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Can the username calling a function be made available within the function?
I'd like to be able to have a PL/pgSQL function defined as SECURITY DEFINER, but still have access to the calling username within the function. Is this possible? I could pass current_user as a parameter, but of course this could easily be bypassed. Is there a way of coding this? Thanks for any help Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] row-level security model
On 2/4/04 4:50 am, John DeSoi [EMAIL PROTECTED] wrote: Marc, On Apr 1, 2004, at 4:21 PM, Marc Durham wrote: Do you think this would work? There are a lot of joins. And I assumed it would need to look up the parent's and teacher's usernames, and that your many-to-many relationships were in tables like students_parent_link. Thanks very much -- this helped get me started. I think I finally came up with something that lets me have modularized access functions where I can combine access but still maintain a single view. Here is a rough example. Let's say I have two access functions contact_students and staff_teaches_students. If the current user is in the contact group it returns the primary keys (integer dbid in my example) of the related students. Similarly, if the current user is on the teaching staff, it returns the keys for all students in his/her classes. So I create a function to combine all of my access functions with union: create or replace function student_access () returns setof integer as ' select * from contact_students() union select * from staff_teaches_students(); ' language sql; Then my view is create view student_v as select student.* from student, student_access() as id where student.dbid = id; Comments/criticisms about design or performance issues? Is there a way to provide column security without creating different views for every possible scenario? Hi John, I don't know if this will fit your needs, but this is how I handled row level security in an application I have. It uses arrays, so may be PostgreSQL specific I think... But basically I have person and group tables CREATE TABLE person ( person_idINT4NOT NULL, .. other fields .. username TEXTNOT NULL, lab_group_id INT4NOT NULL, groups_ids INT[] NULL ); CREATE TABLE groups ( group_id INT4NOT NULL, name TEXTNOT NULL ); Then each object has a base table: CREATE TABLE experiment_base ( expt_id INT4NOT NULL, .. other fields .. owner_id INT NOT NULL, writer_idINT[] NOT NULL, readers_id INT[] NOT NULL ); I can then control who can update the row at the user level, and who can read the row at the group level using a view like so: CREATE OR REPLACE VIEW experiment AS SELECT various fields FROM experiment_base a, person b WHERE a.owner_id = b.person_id AND (readers_id (select groups_ids from person a where a.username = current_user) OR (select person_id from person a where a.username = current_user) = ANY (writer_id) OR owner_id = (select person_id from person a where a.username = current_user)); I then have a couple of functions to add or remove group_id's from the readers_id array, and also to add or remove person_id's from the writer_id array I don't have large numbers of users or groups, so it performs ok... Not sure how the array approach will scale with more though. I don't think this is a classical approach But it seems to work for me. But I would appreciate comments/criticisms from others? Cheers Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] table functions + user defined types
On 27/10/03 3:20 pm, BARTKO, Zoltan [EMAIL PROTECTED] wrote: Ladies and Gentlemen, Please, enlighten me, if you can, in the following matter: I made a type: create type my_type as ( a integer, b integer ); since I have a table: create table my_table ( a integer; ); and I have now a function too: create or replace function my_func ( integer, -- a integer, -- b ) returns setof my_type as ' declare pa alias for $1; pb alias for $2; -- declarations my_valuemy_type; begin my_value.a := pa; my_value.b := pb; return my_value; end; ' language 'plpgsql'; Try this create or replace function my_func ( integer, -- a integer -- b ) returns my_type as ' declare pa alias for $1; pb alias for $2; -- declarations my_valuerecord; begin select into my_value pa, pb; return my_value; end; ' language 'plpgsql'; when I run this darling function I get a parse error pointing to the line after begin. What am I doing wrong? I have skimmed through all the manuals, had a look at the postgresql cookbook, no info on this. I just would like to have a function that returns more fields at the same time - add a column to table my_table, where I could occasionally return some value (e.g. error code). How to do this? Thanks for your advice in advance Zoltan Bartko -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] function with tablename parameter
To use dynamic queries you will probably have to use EXECUTE, take a look
here
http://www.postgresql.org/docs/7.3/interactive/plpgsql-statements.html#PLPGS
QL-STATEMENTS-EXECUTING-DYN-QUERIES
Although that page says that EXECUTE does not support SELECT INTO queries,
but you may be able to build something using FOR-IN-EXECUTE as described in
this section:
http://www.postgresql.org/docs/7.3/interactive/plpgsql-control-structures.ht
ml#PLPGSQL-RECORDS-ITERATING
HTH
Adam
hello,
it is possible to write something similar???
create function get_count(varchar(32)) RETURNS int4 AS '
DECLARE
tmp int4;
BEGIN
SELECT COUNT(*) INTO tmp FROM $1;
RETURN tmp;
END;' LANGUAGE 'plpgsql';
SELECT get_count('k_part');
SQL error:
ERROR: parser: parse error at or near $1 at character 24
thanx, miso
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [GENERAL] OS X installation with readline support
This worked for me... ./configure --with-libs=/sw/lib --with-includes=/sw/include After reading this: http://marc.theaimsgroup.com/?l=postgresql-generalm=103886532224699w=2 It looks like some of you out there have successfully installed postgresql on OS X with readline support. I have installed readline via Fink but the compiler can't find it. How do I tell it where to look? culley ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
