Re: [HACKERS] Proposal to sync SET ROLE and pg_stat_activity
Hi Bernd, Bernd Helmle wrote: --On Mittwoch, August 27, 2008 09:35:03 +0200 Grant Finnemore [EMAIL PROTECTED] wrote: I have a session pool, where all connections to the database are obtained as a superuser. On issuing connections to the client, we invoke either SET ROLE or SET SESSION AUTHORIZATION and switch to a role with less permissions. This means that we don't have to reserve a connection per user, and we can still use the database access restrictions. But you have to ensure that your session pool is smaller than max_connections, since this will eat up superuser_reserved_connections and would make administrator intervention impossible under certain circumstances. Yes, but that's the easy part. Any reasonable pooling software allows you to set max connections. And why do you need to hack pg_stat_activity, isn't it possible to plug your own view in? Well, pg_stat_activity isn't really the problem here, because as you point out, it's just a view, and I could certainly redefine the view. The limiting factor is that the backend doesn't push the role name changes to the stats subsystem for either SET ROLE or SET SESSION AUTH. An alternative to changing the current behaviour would be to introduce new variables in the backend structures that are sent to the stats subsystem, and which could be read by as yet undefined functions. This would keep existing behaviour, but allow others to obtain the alternative behaviour through the creation of a separate view. Regards, Grant -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal to sync SET ROLE and pg_stat_activity
Hi Alvaro, Alvaro Herrera wrote: Grant Finnemore wrote: Well, pg_stat_activity isn't really the problem here, because as you point out, it's just a view, and I could certainly redefine the view. The limiting factor is that the backend doesn't push the role name changes to the stats subsystem for either SET ROLE or SET SESSION AUTH. Keep in mind that stats are updated only once every 500 ms, and messages have a nontrivial overhead. With your proposed changes, there would be a significant performance overhead to running security definer functions. A possible solution to this would be to publish current_user in shared memory, so that remote processes could read it from there (similar to how current_query is published). Yeah, I was concerned about security definer functions, although I hadn't yet got round to benchmarking the effects. If there is some consensus that from a user perspective this is a reasonable enhancement, I'll pursue the changes using your suggestion of the current_query approach. Regards, Grant -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal to sync SET ROLE and pg_stat_activity
Hi Euler, Euler Taveira de Oliveira wrote: Grant Finnemore escreveu: Invoking pg_stat_activity after the SET ROLE is changed will however leave the usename unchanged. You're right. Because, as you spotted, usename is synonym of session usename. The one problem with this mapping is that per the manual, user is equivalent to current_user, and so it could be argued that usename is equivalent to both of these. SET SESSION AUTHORIZATION behaves similarly, although in that case, it's documented that both session_user and current_user are changed to reflect the new user. Ugh? The manual [1][2] documents the behavior of both commands. Sorry if I wasn't clear here - I agree that the manual documents this behaviour. My intent was to use these to highlight the different between what these display, and what pg_stat_activity displays. I have on occasion used a database pooling scheme that whenever a connection is retrieved from the pool, either a SET ROLE or SET SESSION AUTHORIZATION is issued to enable database level access restrictions. Similarly, when the connection is returned, a RESET instruction is issued. I can't see in your use case the advantage of allowing to show current_user. Perhaps an example would clarify my use case. I have a session pool, where all connections to the database are obtained as a superuser. On issuing connections to the client, we invoke either SET ROLE or SET SESSION AUTHORIZATION and switch to a role with less permissions. This means that we don't have to reserve a connection per user, and we can still use the database access restrictions. Now, if someone starts a query that takes a long time, with the changes I'm proposing, I can see which user is running that query. As it is now, all I see is a list of connections issued to a superuser. IMHO, it would be advantageous to be able to display which connections are in use by a given user through the pg_stat_activity view. Isn't it embarrassing if, for example, mary queries pg_stat_activity and sees that I'm using her role, is it? I'm not against exposing this information but I think it could be superuser-only. Well, it could be argued that if it's embarrassing, then the user using that role is doing something illicit. Also, if we have rights to switch to another role, then surely that's an intended use? There are two ways in which this could be done. Firstly, we could alter the current usename field in the view. This would keep the view definition the same, but would alter the semantics, which could affect existing clients. Alternatively, we could introduce another column that would reflect the role name. Why not add another column: current_usename? I would object if we've intended to change the view semantics. Yeah, my thoughts would be to map user/current_user to usename, and create a new column for session_user called susename (or something similar.) Behaviour would be something along the following lines... 1. Login as user foo 2. user/current_user = foo, session_user = foo 2a. select usename, susename from pg_stat_activity = (foo, foo) 3. SET ROLE bar 4. user/current_user = bar, session_user = foo 4a. select usename, susename from pg_stat_activity = (bar, foo) .. alternatively .. 1. Login as user foo 2. user/current_user = foo, session_user = foo 2a. select usename, susename from pg_stat_activity = (foo, foo) 3. SET SESSION AUTHORIZATION bar 4. user/current_user = bar, session_user = bar 4a. select usename, susename from pg_stat_activity = (bar, bar) Regards, Grant -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Proposal to sync SET ROLE and pg_stat_activity
Hi,
In the manual for SET ROLE, it's noted that an invocation of SET ROLE
will leave the session_user unchanged, but will change the current_user.
Invoking pg_stat_activity after the SET ROLE is changed will however
leave the usename unchanged. (Also from the manual we note that a
snapshot is taken at the first call, although in the case of
current_query and others, the field is updated at regular intervals)
SET SESSION AUTHORIZATION behaves similarly, although in that case,
it's documented that both session_user and current_user are changed
to reflect the new user.
An example:-
test=# select current_user, session_user;
current_user | session_user
--+--
grant| grant
(1 row)
test=# select usename from pg_stat_activity;
usename
-
grant
(1 row)
test=# set session role bob;
SET
test= select current_user, session_user;
current_user | session_user
--+--
bob | grant
(1 row)
test= select usename from pg_stat_activity;
usename
-
grant
(1 row)
I have on occasion used a database pooling scheme that whenever a
connection is retrieved from the pool, either a SET ROLE or SET
SESSION AUTHORIZATION is issued to enable database level access
restrictions. Similarly, when the connection is returned, a RESET
instruction is issued.
IMHO, it would be advantageous to be able to display which
connections are in use by a given user through the pg_stat_activity
view. Looking through the archives, I've found one other request
for this which AFAICS wasn't answered.
http://archives.postgresql.org/pgsql-bugs/2007-04/msg00035.php
There are two ways in which this could be done. Firstly, we could
alter the current usename field in the view. This would keep the
view definition the same, but would alter the semantics, which could
affect existing clients. Alternatively, we could introduce another
column that would reflect the role name.
I attach a patch that kinda works for the SET SESSION AUTH case, and
will undertake to complete the work should there be some general
support for this proposal.
Comments?
Regards,
Grant Finnemore
Index: src/backend/postmaster/pgstat.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/postmaster/pgstat.c,v
retrieving revision 1.179
diff -c -r1.179 pgstat.c
*** src/backend/postmaster/pgstat.c 15 Aug 2008 08:37:39 - 1.179
--- src/backend/postmaster/pgstat.c 25 Aug 2008 09:08:46 -
***
*** 2264,2269
--- 2264,2294
}
/* --
+ * pgstat_report_change_authorization()
+ *
+ * Called from *** to report the changing of session authorization.
+ * --
+ */
+ void
+ pgstat_report_change_authorization(void)
+ {
+ volatile PgBackendStatus *beentry = MyBEEntry;
+
+ if (!pgstat_track_activities || !beentry)
+ return;
+
+ /*
+ * Update my status entry, following the protocol of bumping
+ * st_changecount before and after. We use a volatile pointer
+ * here to ensure the compiler doesn't try to get cute.
+ */
+ beentry-st_changecount++;
+ beentry-st_userid = GetSessionUserId();
+ beentry-st_changecount++;
+ Assert((beentry-st_changecount 1) == 0);
+ }
+
+ /* --
* pgstat_report_waiting() -
*
*Called from lock manager to report beginning or end of a lock wait.
Index: src/backend/utils/init/miscinit.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/utils/init/miscinit.c,v
retrieving revision 1.167
diff -c -r1.167 miscinit.c
*** src/backend/utils/init/miscinit.c 27 Mar 2008 17:24:16 - 1.167
--- src/backend/utils/init/miscinit.c 25 Aug 2008 09:08:46 -
***
*** 349,354
--- 349,357
/* We force the effective user IDs to match, too */
OuterUserId = userid;
CurrentUserId = userid;
+
+ /* Let the stats subsystem know of the change */
+ pgstat_report_change_authorization();
}
Index: src/include/pgstat.h
===
RCS file: /projects/cvsroot/pgsql/src/include/pgstat.h,v
retrieving revision 1.78
diff -c -r1.78 pgstat.h
*** src/include/pgstat.h15 Aug 2008 08:37:40 - 1.78
--- src/include/pgstat.h25 Aug 2008 09:08:46 -
***
*** 626,631
--- 626,632
extern void pgstat_report_activity(const char *what);
extern void pgstat_report_xact_timestamp(TimestampTz tstamp);
+ extern void pgstat_report_change_authorization(void);
extern void pgstat_report_waiting(bool waiting);
extern const char *pgstat_get_backend_current_activity(int pid, bool
checkUser);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Backend crash during explain
Hi, This is on Intel OSX, anon CVS download today. Build process:- 1. make distclean 2. ./configure --enable-debug --enable-cassert --enable-integer-datetimes --prefix=/Users/grant/Development/bin/pgsql --enable-depend 3. make all install The query with no EXPLAIN (ANALYSE) completes fine. The query with EXPLAIN ANALYSE completes fine. foo=# explain analyse select this_.id as id6_2_, this_1_.created_at as created2_6_2_, this_1_.created_by as created3_6_2_, this_1_.updated_at as updated4_6_2_, this_1_.updated_by as updated5_6_2_, this_1_.from_date as from6_6_2_, this_1_.party_id as party8_6_2_, this_1_.thru_date as thru7_6_2_, this_1_.type_id as type9_6_2_, this_.tag as tag14_2_, this_.taggedBy_id as taggedBy4_14_2_, this_.taggedDate as taggedDate14_2_, partyrolet2_.id as id3_0_, partyrolet2_.created_at as created2_3_0_, partyrolet2_.created_by as created3_3_0_, partyrolet2_.updated_at as updated4_3_0_, partyrolet2_.updated_by as updated5_3_0_, partyrolet2_.description as descript6_3_0_, partyrolet2_.name as name3_0_, tagimplant3_.id as id6_1_, tagimplant3_1_.created_at as created2_6_1_, tagimplant3_1_.created_by as created3_6_1_, tagimplant3_1_.updated_at as updated4_6_1_, tagimplant3_1_.updated_by as updated5_6_1_, tagimplant3_1_.from_date as from6_6_1_, tagimplant3_1_.party_id as party8_6_1_, tagimplant 3_1_.thru_date as thru7_6_1_, tagimplant3_1_.type_id as type9_6_1_ from tagged_asset this_ inner join party_role this_1_ on this_.id=this_1_.id inner join party_role_type partyrolet2_ on this_1_.type_id=partyrolet2_.id left outer join tag_implanter tagimplant3_ on this_.taggedBy_id=tagimplant3_.id left outer join party_role tagimplant3_1_ on tagimplant3_.id=tagimplant3_1_.id where (lower(this_.tag) like '1f76%') limit 100; QUERY PLAN -- Limit (cost=8.31..24.50 rows=1 width=3748) (actual time=23.057..209.191 rows=77 loops=1) - Nested Loop (cost=8.31..24.50 rows=1 width=3748) (actual time=23.055..209.142 rows=77 loops=1) - Nested Loop Left Join (cost=8.31..24.22 rows=1 width=2170) (actual time=23.036..208.326 rows=77 loops=1) - Nested Loop Left Join (cost=8.31..18.62 rows=1 width=1098) (actual time=23.033..208.204 rows=77 loops=1) - Merge Join (cost=8.31..10.34 rows=1 width=1094) (actual time=23.024..208.015 rows=77 loops=1) Merge Cond: (this_1_.id = this_.id) - Index Scan Backward using party_role_pkey on party_role this_1_ (cost=0.00..18672.18 rows=581325 width=1076) (actual time=0.102..142.963 rows=240384 loops=1) - Sort (cost=8.31..8.32 rows=1 width=22) (actual time=0.856..0.902 rows=77 loops=1) Sort Key: this_.id Sort Method: quicksort Memory: 20kB - Index Scan using tagged_asset_tag_key on tagged_asset this_ (cost=0.01..8.30 rows=1 width=22) (actual time=0.109..0.739 rows=77 loops=1) Index Cond: ((lower((tag)::text) = '1f76'::text) AND (lower((tag)::text) '1f77'::text)) Filter: (lower((tag)::text) ~~ '1f76%'::text) - Index Scan using tag_implanter_pkey on tag_implanter tagimplant3_ (cost=0.00..8.27 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=77) Index Cond: (this_.taggedby_id = tagimplant3_.id) - Index Scan using party_role_pkey on party_role tagimplant3_1_ (cost=0.00..5.59 rows=1 width=1076) (actual time=0.000..0.000 rows=0 loops=77) Index Cond: (tagimplant3_.id = tagimplant3_1_.id) - Index Scan using party_role_type_pkey on party_role_type partyrolet2_ (cost=0.00..0.27 rows=1 width=1578) (actual time=0.008..0.009 rows=1 loops=77) Index Cond: (partyrolet2_.id = this_1_.type_id) Total runtime: 209.699 ms (20 rows) However, with just EXPLAIN (no ANALYSE) foo=# explain select this_.id as id6_2_, this_1_.created_at as created2_6_2_, this_1_.created_by as created3_6_2_, this_1_.updated_at as updated4_6_2_, this_1_.updated_by as updated5_6_2_, this_1_.from_date as from6_6_2_, this_1_.party_id as party8_6_2_, this_1_.thru_date as thru7_6_2_, this_1_.type_id as type9_6_2_, this_.tag as tag14_2_, this_.taggedBy_id as taggedBy4_14_2_, this_.taggedDate as taggedDate14_2_, partyrolet2_.id as id3_0_, partyrolet2_.created_at as created2_3_0_, partyrolet2_.created_by as created3_3_0_, partyrolet2_.updated_at as updated4_3_0_, partyrolet2_.updated_by
Re: [HACKERS] SQL-Invoked Procedures for 8.1
Quoth the JDBC spec:
public interface CallableStatement
extends PreparedStatement
The interface used to execute SQL stored procedures. The JDBC API provides a
stored procedure SQL escape syntax that allows stored procedures to be called
in a standard way for all RDBMSs. This escape syntax has one form that includes
a result parameter and one that does not. If used, the result parameter must be
registered as an OUT parameter. The other parameters can be used for input,
output or both. Parameters are referred to sequentially, by number, with the
first parameter being 1.
{?= call procedure-name[arg1,arg2, ...]}
{call procedure-name[arg1,arg2, ...]}
IN parameter values are set using the set methods inherited from
PreparedStatement. The type of all OUT parameters must be registered prior to
executing the stored procedure; their values are retrieved after execution via
the get methods provided here.
A CallableStatement can return one ResultSet object or multiple ResultSet
objects. Multiple ResultSet objects are handled using operations inherited from
Statement.
For maximum portability, a call's ResultSet objects and update counts should be
processed prior to getting the values of output parameters.
Regards,
Grant
Gavin Sherry wrote:
On Thu, 23 Sep 2004, Grant Finnemore wrote:
Hi Gavin,
Although I have not read the SQL 2003 spec, my recollection of other database
products' stored procs differed from your description in one significant way,
namely that they could return multiple (and varied) sets of rows.
For example, a stored proc could do a SELECT over foo and then a SELECT over
bar and return the tuples of both foo and bar. (each having different column
counts, types, etc)
The JDBC interfaces would appear to illustrate this point.
(In CallableStatement)
A CallableStatement can return one ResultSet object or multiple ResultSet
objects. Multiple ResultSet objects are handled using operations inherited
from Statement.
I read the JDBC 3.0 spec and didn't see this. Like with all specs, some
details are hard to find. However, from what I've seen in the spec, I
think they have functions in mind here. That being said, I can't think how
SQL2003 would allow such behaviour. If you could show us an example,
that'd be great.
Thanks,
Gavin
---(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
---(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: [HACKERS] SQL-Invoked Procedures for 8.1
Hi Magnus,
Yes, this is the situation that I have been thinking about. Specifically
when a single stored procedure returns many recordsets.
Perhaps I should also clarify that the spec I have been using is
the JDK javadoc documentation.
Using java with Magnus' procedure:
CallableStatement cs = connection.prepareCall(call get_info_for_user ?);
cs.setString(1, test);
if(cs.execute()) {
ResultSet rs = cs.getResultSet();
while(rs != null) {
// Process rs
}
}
Regards,
Grant
Magnus Hagander wrote:
[snip]
Not a user of JDBC, but this is fairly common in the ADO/ADO.NET world
with MS SQL Server as well (not sure about other dbs and .NET - I'ev
only used it with mssql and pgsql)... As for an example, something along
the line of (though in my cases usually with a *lot* more parameters):
--
CREATE PROCEDURE get_info_for_user(@userid varchar(16)) AS
SELECT something FROM contentstable WHERE [EMAIL PROTECTED]
SELECT whatever,somethingelse FROM anothertable WHERE
something=anything
--
You get the point :-)
Then in my .net code I'd do a simple:
SqlDataReader rdr = cmd.ExecuteReader();
... process first result ...
rdr.NextResult();
... process second result...
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] SQL-Invoked Procedures for 8.1
Hi Gavin, Although I have not read the SQL 2003 spec, my recollection of other database products' stored procs differed from your description in one significant way, namely that they could return multiple (and varied) sets of rows. For example, a stored proc could do a SELECT over foo and then a SELECT over bar and return the tuples of both foo and bar. (each having different column counts, types, etc) The JDBC interfaces would appear to illustrate this point. (In CallableStatement) A CallableStatement can return one ResultSet object or multiple ResultSet objects. Multiple ResultSet objects are handled using operations inherited from Statement. Regards, Grant Gavin Sherry wrote: Hi all, Following is a proposal to implement what SQL2003 calls 'SQL-Invoked Procedures' and what most people refer to as stored procedures. Fujitsu will be funding Neil Conway and I to work on this feature. [lots of interesting detail] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Failed assertion, CVS head
Hi, I am using a version of PostgreSQL compiled from a CVS update of yesterday, and compiled with make clean all make install One client connection to the database doing routine and low volume population scripts (using schemas) After several normal runs of the population script, a run caused the following trap, and an associated backend halt. TRAP: FailedAssertion(!(((ntp)-t_data)-t_infomask 0x0010), File: catcache.c, Line: 1728) I know this is not a lot of data for a determination of cause, but I have only had a single instance of this happening, and thought it should at least be documented. Regards, Grant ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Failed assertion, CVS head
It's happened again, and in both cases seems to be on a call to VACUUM FULL Grant Finnemore wrote: Hi, I am using a version of PostgreSQL compiled from a CVS update of yesterday, and compiled with make clean all make install One client connection to the database doing routine and low volume population scripts (using schemas) After several normal runs of the population script, a run caused the following trap, and an associated backend halt. TRAP: FailedAssertion(!(((ntp)-t_data)-t_infomask 0x0010), File: catcache.c, Line: 1728) I know this is not a lot of data for a determination of cause, but I have only had a single instance of this happening, and thought it should at least be documented. Regards, Grant ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Failed assertion, CVS head
I'm afraid that I did not get a core dump. Sorry. My normal configure includes both debug and cassert - is there anything else I should set to ensure core dumps are generated? Regards, Grant Tom Lane wrote: Grant Finnemore [EMAIL PROTECTED] writes: TRAP: FailedAssertion(!(((ntp)-t_data)-t_infomask 0x0010), File: catcache.c, Line: 1728) This seems moderately impossible :-(. Did you get a core dump? If so please provide a stack backtrace. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Failed assertion, CVS head
Ok, will do. Thanks. Tom Lane wrote: Grant Finnemore [EMAIL PROTECTED] writes: I'm afraid that I did not get a core dump. Sorry. My normal configure includes both debug and cassert - is there anything else I should set to ensure core dumps are generated? Check ulimit -c in the postmaster's environment. Personally I always put ulimit -c unlimited into the postmaster start script. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Performance with new nested-xacts code
Hi Tom, As requested - although the results are all over the place... :-( One interesting factor in these tests is that the max tps without the new code was 74.7, with the new code, 85.8. This is a Sony Laptop, slow IDE disk, Fedora Core 2 [EMAIL PROTECTED] pgsql-HEAD]$ uname -a Linux localhost.localdomain 2.6.6-1.435 #1 Mon Jun 14 09:09:07 EDT 2004 i686 i686 i386 GNU/Linux ./bin/postmaster -F HTH. Regards, Grant -- PRE NESTED XACTS [EMAIL PROTECTED] pgbench]$ ./pgbench -c 5 -t 1000 bench starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 10 number of clients: 5 number of transactions per client: 1000 number of transactions actually processed: 5000/5000 tps = 74.632059 (including connections establishing) tps = 74.710309 (excluding connections establishing) [EMAIL PROTECTED] pgbench]$ ./pgbench -c 5 -t 1000 bench starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 10 number of clients: 5 number of transactions per client: 1000 number of transactions actually processed: 5000/5000 tps = 61.405658 (including connections establishing) tps = 61.471754 (excluding connections establishing) [EMAIL PROTECTED] pgbench]$ ./pgbench -c 5 -t 1000 bench starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 10 number of clients: 5 number of transactions per client: 1000 number of transactions actually processed: 5000/5000 tps = 59.702545 (including connections establishing) tps = 59.754499 (excluding connections establishing) [EMAIL PROTECTED] pgbench]$ ./pgbench -c 5 -t 1000 bench starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 10 number of clients: 5 number of transactions per client: 1000 number of transactions actually processed: 5000/5000 tps = 54.531685 (including connections establishing) tps = 54.584432 (excluding connections establishing) -- POST NESTED XACTS [EMAIL PROTECTED] pgbench]$ ./pgbench -c 5 -t 1000 bench starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 10 number of clients: 5 number of transactions per client: 1000 number of transactions actually processed: 5000/5000 tps = 72.656915 (including connections establishing) tps = 72.732723 (excluding connections establishing) [EMAIL PROTECTED] pgbench]$ ./pgbench -c 5 -t 1000 bench starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 10 number of clients: 5 number of transactions per client: 1000 number of transactions actually processed: 5000/5000 tps = 85.687383 (including connections establishing) tps = 85.822281 (excluding connections establishing) [EMAIL PROTECTED] pgbench]$ ./pgbench -c 5 -t 1000 bench starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 10 number of clients: 5 number of transactions per client: 1000 number of transactions actually processed: 5000/5000 tps = 59.479127 (including connections establishing) tps = 59.540478 (excluding connections establishing) [EMAIL PROTECTED] pgbench]$ ./pgbench -c 5 -t 1000 bench starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 10 number of clients: 5 number of transactions per client: 1000 number of transactions actually processed: 5000/5000 tps = 51.675145 (including connections establishing) tps = 51.715526 (excluding connections establishing) Tom Lane wrote: [snip] Can anyone else reproduce these results? The test case I'm using is pgbench -i -s 10 bench followed by repeated pgbench -c 5 -t 1000 bench I've built PG with --enable-debug and --enable-cassert, and am running with -F (fsync off) but otherwise absolutely factory-stock postgresql.conf. The hardware is a not-so-new-anymore Dell P4 with run-of-the-mill IDE disk drive, running RHL 8.0. Obviously none of this is tuned at all, but the question is why did CVS tip get faster when it should by rights be slower. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Compile breakage
RHL 9.0 cvs HEAD, fresh update make maintainer-clean ./configure --with-java --prefix=/home/grant/bin/pgsql/ \ --with-integer-datetimes --enable-debug --enable-cassert make yields: gcc -O2 -fno-strict-aliasing -g -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../src/include -D_GNU_SOURCE -c -o vacuum.o vacuum.c vacuum.c: In function `vac_init_rusage': vacuum.c:3122: storage size of `tz' isn't known vacuum.c:3125: warning: implicit declaration of function `gettimeofday' vacuum.c:3122: warning: unused variable `tz' make[3]: *** [vacuum.o] Error 1 Regards, Grant ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] TRAP during backend startup
Hi all, After an installation from CVS tip, I start the postmaster with the -d(anything) flag. The postmaster starts up normally. I then try to connect using psql, and either a TRAP is triggered (not using -A 0) or an invalid cache id is found (using -A 0) When I start the postmaster without the -d flag, I have no problems in connecting from psql. Any ideas? Regards, Grant Necessary info: . Sources are CVS tip, pulled today . uname -a Linux localhost.localdomain 2.4.20-19.9 #1 Tue Jul 15 17:18:13 EDT 2003 i686 i686 i386 GNU/Linux . gcc --version gcc (GCC) 3.2.2 20030222 (Red Hat Linux 3.2.2-5) . make maintainer-clean check install ./configure --with-java --prefix=/home/grant/bin/pgsql/ --with-integer-datetimes --enable-debug --enable-cassert [EMAIL PROTECTED] pgsql-HEAD]$ postmaster -d1 LOG: database system was shut down at 2004-04-07 12:51:06 SAST LOG: checkpoint record is at 0/9DCE0C LOG: redo record is at 0/9DCE0C; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 465; next OID: 17208 LOG: database system is ready Now, from another console do : psql -l TRAP: BadState(!(((bool) ((CurrentUserId) != 0))), File: miscinit.c, Line: 252) LOG: server process (PID 16802) was terminated by signal 6 LOG: terminating any other active server processes [EMAIL PROTECTED] pgsql-HEAD]$ postmaster -A 0 -d1 LOG: database system was shut down at 2004-04-07 12:51:36 SAST LOG: checkpoint record is at 0/9DCE8C LOG: redo record is at 0/9DCE8C; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 465; next OID: 17208 LOG: database system is ready FATAL: invalid cache id: 30 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] protocol change in 7.4
Questions have arisen during discussions about errors relating
to how to support error codes without changing the FE/BE
protocols. (see TODO.detail/error)
Now that the protocol is up for revision, how about supporting
sql state strings, error codes, and other information directly in
the protocol.
Regards,
Grant
Neil Conway wrote:
There has been some previous discussion of changing the FE/BE protocol
in 7.4, in order to fix several problems. I think this is worth doing:
if we can resolve all these issues in a single release, it will lessen
the upgrade difficulties for users.
I'm aware of the following problems that need a protocol change to fix
them:
(1) Add an optional textual message to NOTIFY
(2) Remove the hard-coded limits on database and user names
(SM_USER, SM_DATABASE), replace them with variable-length
fields.
(3) Remove some legacy elements in the startup packet
('unused' can go -- perhaps 'tty' as well). I think the
'length' field of the password packet is also not used,
but I'll need to double-check that.
(4) Fix the COPY protocol (Tom?)
(5) Fix the Fastpath protocol (Tom?)
(6) Protocol-level support for prepared queries, in order to
bypass the parser (and maybe be more compatible with the
implementation of prepared queries in other databases).
(7) Include the current transaction status, since it's
difficult for the client app to determine it for certain
(Tom/Bruce?)
If I've missed anything or if there is something you think we should
add, please let me know.
I can implement (1), (2), (3), and possibly (7), if someone can tell
me exactly what is required (my memory of the discussion relating to
this is fuzzy). The rest is up for grabs.
Finally, how should we manage the transition? I wasn't around for the
earlier protocol changes, so I'd appreciate any input on steps we can
take to improve backward-compatibility.
Cheers,
Neil
smime.p7s
Description: S/MIME Cryptographic Signature
Re: [HACKERS] Does setof record in plpgsql work well in 7.3?
CREATE OR REPLACE FUNCTION myfunc(integer) RETURNS SETOF record AS ' DECLARE rec record; BEGIN FOR rec IN SELECT * FROM test WHERE a = $1 LOOP RAISE NOTICE ''a = %, b = %'',rec.a, rec.b; RETURN NEXT rec; END LOOP; RETURN null; END; ' LANGUAGE 'plpgsql'; SELECT * FROM myfunc(1) AS t(a integer, b text); Note the use of the RETURN NEXT rec line in the body of the for loop, and also the RETURN null at the end. It is also possible to create typed returns, so in this case, in the declare body, the following would be valid. DECLARE rec test%ROWTYPE; The function definition then becomes:- CREATE OR REPLACE FUNCTION myfunc(integer) RETURNS SETOF test ... One can also create your own return type in the following manner. create type my_return_type as ( foo integer, bar text ); Now, the declare block has the following:- DECLARE rec my_return_type%ROWTYPE The function definition then becomes:- CREATE OR REPLACE FUNCTION myfunc(integer) RETURNS SETOF my_return_type ... Regards, Grant Finnemore Masaru Sugawara wrote: Hi, all Does 7.3 support SETOF RECORD in plpgsql ? As far as I test it, a function using it in plpgsql always seems to return no row. On the other hand, a sql function returns correct rows. If 7.3 doesn't support it in plpgsql, I would think plpgsql needs to raise an error rather than return 0 rows message. Am I misunderstanding how to use? -- CREATE TABLE test (a integer, b text); INSERT INTO test VALUES(1, 'function1'); INSERT INTO test VALUES(2, 'function2'); INSERT INTO test VALUES(1, 'function11'); INSERT INTO test VALUES(2, 'function22'); CREATE OR REPLACE FUNCTION myfunc(integer) RETURNS SETOF record AS ' DECLARE rec record; BEGIN FOR rec IN SELECT * FROM test WHERE a = $1 LOOP RAISE NOTICE ''a = %, b = %'',rec.a, rec.b; END LOOP; RETURN rec; END; ' LANGUAGE 'plpgsql'; SELECT * FROM myfunc(1) AS t(a integer, b text); NOTICE: a = 1, b = function1 NOTICE: a = 1, b = function11 a | b ---+--- (0 rows) CREATE OR REPLACE FUNCTION myfunc(integer) RETURNS SETOF record AS ' SELECT * FROM test WHERE a = $1; ' LANGUAGE 'sql'; SELECT * FROM myfunc(1) AS t(a integer, b text); a | b ---+ 1 | function1 1 | function11 (2 rows) Regards, Masaru Sugawara ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] smime.p7s Description: S/MIME Cryptographic Signature
Re: [HACKERS] Failure to recognise new database
Tom Lane wrote: Thomas Lockhart [EMAIL PROTECTED] writes: Is it just me? I'm pretty sure I saw something similar on a newly initialized database. Are you guys running with WAL enabled? If so, this is probably the BufferSync issue that Hiroshi thought I broke a couple days ago. Let me know... Yes, I am running WAL enabled. regards, tom lane Regards, Grant -- Poorly planned software requires a genius to write it and a hero to use it. Grant Finnemore BSc(Eng) (mailto:[EMAIL PROTECTED]) Software Engineer Universal Computer Services Tel (+27)(11)712-1366PO Box 31266 Braamfontein 2017, South Africa Cell (+27)(82)604-553620th Floor, 209 Smit St., Braamfontein Fax (+27)(11)339-3421Johannesburg, South Africa
[HACKERS] Failure to recognise new database
I did a CVS checkout today, and the following database creation fails. In psql:- You are now connected to database template1 as user postgres. template1=# select version(); version PostgreSQL 7.1devel on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66 (1 row) template1=# create database test; CREATE DATABASE template1=# \c test FATAL 1: Database "test" does not exist in the system catalog. Previous connection kept Now restart the postmaster template1=# \c test You are now connected to database test. Is it just me? Regards, Grant -- Poorly planned software requires a genius to write it and a hero to use it. Grant Finnemore BSc(Eng) (mailto:[EMAIL PROTECTED]) Software Engineer Universal Computer Services Tel (+27)(11)712-1366PO Box 31266 Braamfontein 2017, South Africa Cell (+27)(82)604-553620th Floor, 209 Smit St., Braamfontein Fax (+27)(11)339-3421Johannesburg, South Africa
[HACKERS] Re: [SQL] renaming columns... danger?
Just tested this on latest devel. version, and there does seem to be a
problem.
[]$ psql test
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
test=# select version();
version
PostgreSQL 7.1devel on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)
test=# create table a ( aa serial primary key );
NOTICE: CREATE TABLE will create implicit sequence 'a_aa_seq' for
SERIAL column 'a.aa'
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'a_pkey'
for table 'a'
CREATE
test=# alter TABLE a RENAME aa to new_aa;
ALTER
[]$ pg_dump test
--
-- Selected TOC Entries:
--
\connect - gaf
--
-- TOC Entry ID 2 (OID 20352)
--
-- Name: "a_aa_seq" Type: SEQUENCE Owner: gaf
--
CREATE SEQUENCE "a_aa_seq" start 1 increment 1 maxvalue 2147483647
minvalue 1 cache 1 ;
--
-- TOC Entry ID 4 (OID 20370)
--
-- Name: a Type: TABLE Owner: gaf
--
CREATE TABLE "a" (
"new_aa" integer DEFAULT nextval('"a_aa_seq"'::text) NOT NULL,
PRIMARY KEY ("aa")
);
--
-- Data for TOC Entry ID 5 (OID 20370) TABLE DATA a
--
-- Disable triggers
UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" ~* 'a';
COPY "a" FROM stdin;
\.
-- Enable triggers
BEGIN TRANSACTION;
CREATE TEMP TABLE "tr" ("tmp_relname" name, "tmp_reltriggers" smallint);
INSERT INTO "tr" SELECT C."relname", count(T."oid") FROM "pg_class" C,
"pg_trigger" T WHERE C."oid" = T."tgrelid" AND C."relname" ~* 'a' GROUP
BY 1;
UPDATE "pg_class" SET "reltriggers" = TMP."tmp_reltriggers" FROM "tr"
TMP WHERE "pg_class"."relname" = TMP."tmp_relname";
DROP TABLE "tr";
COMMIT TRANSACTION;
--
-- TOC Entry ID 3 (OID 20352)
--
-- Name: "a_aa_seq" Type: SEQUENCE SET Owner:
--
SELECT setval ('"a_aa_seq"', 1, 'f');
Michael Teter wrote:
hi.
I just discovered that doing an alter table ... alter
column (to rename a column) does not do a complete
rename throughout the database.
for example, say you have table a, with columns b and
c. b is your primary key.
now rename b to new_b. if you do a dump of the schema
after you rename, you'll find that you can't reload
that schema because at the bottom of the definition of
table a you have PRIMARY KEY ("b").
shouldn't rename update any index and key definitions?
also, and this may actually the source of the problem,
while scanning my full (schema and data) dump, I
noticed that the contents of table pga_layout also had
the old values of columns that I have renamed.
I'm very frightened right now, because I'm rather
dependent upon my database right now. I don't like
the thought that my database is corrupt at the schema
level.
michael
__
Do You Yahoo!?
Yahoo! Messenger - Talk while you surf! It's FREE.
http://im.yahoo.com/
--
Poorly planned software requires a genius to write it
and a hero to use it.
Grant Finnemore BSc(Eng) (mailto:[EMAIL PROTECTED])
Software Engineer Universal Computer Services
Tel (+27)(11)712-1366PO Box 31266 Braamfontein 2017, South Africa
Cell (+27)(82)604-553620th Floor, 209 Smit St., Braamfontein
Fax (+27)(11)339-3421Johannesburg, South Africa
