Re: [GENERAL] How feasible is this?
On Fri, 2010-05-21 at 16:08 +0800, Craig Ringer wrote: My non-expert feeling is that you could possibly extend a predicate locking scheme to do this. It's something that'd maybe be possible by hooking into the predicate locking schemes being being designed to support true serializability in Pg (see periodic discussion on -hackers) but those locking schemes aren't in the main PG code yet. Thanks for that. Gives me a place to start looking! It might be a good idea to take a few steps back and look at what you are trying to achieve with this. Why do you want it? What for? What problem will it solve for you? It's not a typical application-level problem. I'm playing with the idea of extending the transactional memory system in the Haskell programming language so that database access can be done inside of an application atomic block. Currently, database access counts as I/O, and therefore must be done outside of atomic blocks, and this leads to a somewhat strained programming model for applications combining transactional memory with databases. Haskell's transactional memory provides exactly the feature I'm asking for: specifically, there is a retry action, which rolls back a transaction, blocks until there's some change that makes it likely that the transaction will behave differently in the future, and then retries it. This turns out to be very useful for transactional memory. Whether it's useful for database access or not may be an open question, but it seems very messy to say don't retry if you've touched the database, since the point here is to be composable and not make people worry about the implementation details of some other part of their transactions. -- Chris Smith -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How feasible is this?
I'm writing in desperate hope that something like this exists... because if so, it would make my life a lot easier. I want to be able to: a) Roll back a transaction b) Receive a notification when retrying the exact same transaction might cause different data to be returned from something that was done up to the point of the rollback; i.e., some result set, update count, etc. might be different. It's okay if (b) is overly sensitive. For example, one technically correct (but disappointing) answer would be to get that notification immediately on the rollback; but that would cause the application to spin and retry the same transaction in a loop with no delays, which is hardly ideal to say the least. Any ideas on doing better than that? -- Chris Smith -- 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] Bigtime scaling of Postgresql (cluster and stuff I suppose)
Ever read anything on how myspace is laid out? The big ones need replication to handle the traffic. Actually no. http://highscalability.com/livejournal-architecture Using MySQL replication only takes you so far. (Yeh it's mysql but the point is valid regardless). You can't keep adding read slaves and scale. A lot use sharding now to keep scaling (limiting to X users/accounts per database system and just keep adding more database servers for the next X accounts). Myspace info here: http://highscalability.com/myspace-architecture At 3mill users: - split its user base into chunks of 1 million accounts and put all the data keyed to those accounts in a separate instance of SQL Server I'm sure there's replication behind the scenes to help with read-queries but it's definitely not a magic wand that will fix everything. -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] jdbc pg_hba.conf error
On 5/31/07, Bhavana.Rakesh [EMAIL PROTECTED] wrote: Ok, I confirmed that I'm editing the right pg_hba.conf file. I made sure that there are no other postmasters running. I made sure that there is a user called 'brakesh'. I restart the postmaster everytime I make any changes to pg_hba.conf file. But still same results! [EMAIL PROTECTED] ~/db_connect]$ psql -U brakesh -h 127.0.0.1 -d testing123 psql: FATAL: no pg_hba.conf entry for host 127.0.0.1, user brakesh, database testing123, SSL off [EMAIL PROTECTED] ~/db_connect]$ psql -p 5000 testing123 Welcome to psql 7.4.17, the PostgreSQL interactive terminal. Why are you specifying the port number when you don't include the host? What happens if you do include the port: psql -U brakesh -p 5000 -h 127.0.0.1 -d testing123 -- Postgresql php tutorials http://www.designmagick.com/ ---(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] what is the default username password for PostgreSQL, which was installed with FC5.
On 10/27/06, Purusothaman A [EMAIL PROTECTED] wrote: Thanks for all of your valuable replies. Please tell me, what should i do to login with syntax psql -Uusername -h192.168.2.2 -dusername(database name). Because I should be able to login from any login and also from any system. Set up your pg_hba.conf file to what you need. I have a small article about this on my site: http://www.designmagick.com/article/4/ The postgres documentation is here: http://www.postgresql.org/docs/current/static/client-authentication.html -- Postgresql php tutorials http://www.designmagick.com/ ---(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] Best approach for a gap-less sequence
On 8/12/06, Jorge Godoy [EMAIL PROTECTED] wrote: Hi! I was trying to solve a problem on an old system and realized that there might be some better approach for doing what I need. We have some documents that need to be ordered sequentially and without gaps. I could use a sequence, but if the transaction fails then when I rollback the sequence will already have been incremented. So, today I have a control table and I acquire a SHARE ROW EXCLUSIVE lock to it, read the value, increase it, do what I need and then I COMMIT the transaction, ensuring that the sequence has no gaps. Is there a better way to guarantee that there will be no gaps in my sequence if something goes wrong with my transaction? Why does it matter? I assume there is a reason you need it like this.. -- Postgresql php tutorials http://www.designmagick.com/ ---(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] pgsql user change to postgres
On 7/2/06, Joe [EMAIL PROTECTED] wrote: Hi, I started using PostgreSQL (8.0) about a year ago on Windows. Following the installation instructions, I created a 'postgres' user (BTW, this was based on the Short Version instructions for UNIX, i.e., adduser postgres --I'm not sure it this is made explicit elsewhere, even for UNIX). I'm now migrating to FreeBSD and was surprised to find that the port used 'pgsql' as the user. The maintainer said that was done to ensure backward compatibility because that *was* the original name. Since I didn't need to be backward compatible (and my Windows dbs already used 'postgres'), I tried to bypass that (sort of) requirement by renaming 'pgsql' to 'postgres' (in the passwd file) and changing the postgresql_user variable used in the rc startup file. That was OK until I tried to build 8.1.4_1. I figured out how to tweak the build files to stick with 'postgres' but then I realized I'd have to patch them every time I'd fetch a new build, so I went back to 'pgsql'. I'm curious about a few things. How long ago was the 'pgsql' to 'postgres' change (and maybe it would be helpful to know the rationale for the backward incompatible decision--I tried searching in the archives but 'pgsql' and 'postgres' are all too common)? Is there any problem with using 'pgsql' vs. 'postgres' (and are there any plans to deprecate or disallow the former at some point)? Are other UNIX/Linux ports in the same boat, or does any Linux port offer users a choice in this matter? Would any change to the build/install procs have to be done through the current port maintainer or are they somewhere in the PostgreSQL source tree (and subject to standard submission/review procedures)? I *think* that's a bsd decision to change the name. All of the linux systems I have used for the last 5-6 years have used 'postgres' as the user. -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Installation problems
On 6/30/06, Victor Escobar [EMAIL PROTECTED] wrote: Hello, I'm going through the elongated instructions of installing pgsql and am stuck at the point where one types: % su - postgres When I type this and type in the password I chose, I get the following error: 'su: no directory' What directory does /etc/passwd have for the postgres user? Does it exist? -- Postgresql php tutorials http://www.designmagick.com/ ---(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] Computing transitive closure of a table
I am doing some preliminary work on the next major release of a piece of software that uses PostgreSQL. As odd as this sounds, it seems that a huge percentage of the new features that have been requested involve computing the transitive closure of a binary relation that's expressed in a database table. For example: - Given a list of relationships of the form X is a direct subgroup of Y, determine the full list of groups of which some group is a (not necessarily direct) subgroup. - Given a list of statements of the form X must happen before Y, determine everything that needs to happen for some objective to be achieved. And the list goes on and on... I'm aware that it's not possible to solve the transitive closure problem using a simple SQL query. Anyone have any recommendations? Are there any thoughts on implementing efficient transitive closures within PostgreSQL? If I wanted to do it, are there preferences on syntax or other such things? My thoughts on an ideal feature would involve being able to create a sort of transitive closure index which could be kept up to date automatically by the database back end. Or should I just punt and let the queries be slow (not a good option, since the group thing is necessary for permission checking, which may happen up to a half-dozen times per HTTP request). Thanks, -- Chris Smith - Lead Software Developer/Technical Trainer MindIQ Corporation ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Computing transitive closure of a table
Oleg Bartunov wrote: Chris, have you seen contrib/ltree ? I hadn't. Thanks! I will look into it further, but I'm currently a bit concerned by the word tree in the title. Many of the problems I'm solving are not trees, though nearly all are DAGs. -- Chris Smith - Lead Software Developer/Technical Trainer MindIQ Corporation ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Computing transitive closure of a table
Thanks for everyone's suggestions. I found the following, which at least seems to meet my needs temporarily. http://citeseer.ist.psu.edu/dong99maintaining.html Should it turn out that this is not feasible to implement via triggers in PostgreSQL, I may be back with more questions and seek out a route that involves modifying the database or other such things. -- Chris Smith - Lead Software Developer/Technical Trainer MindIQ Corporation ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] ean code data type
On 6/7/06, Ottavio Campana [EMAIL PROTECTED] wrote: Is there a data type for ean codes for postgresql 7.4? I found the isbn data type, and I would appreciate something similar for ean codes. If there isn't you can create your own: http://www.postgresql.org/docs/8.1/static/sql-createtype.html -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] postgres in windows
On 6/1/06, Antonios Katsikadamos [EMAIL PROTECTED] wrote: Hi all. My name is Antonios and I am doing an MSc in Advanced computing at Imperial College London. I need to install postgres for my individual project on windows. Would it be a problem to ask one- or -two questions? First of all is there an installer for postgres for windows? http://www.postgresql.org/ftp/binary/v8.1.4/win32/ Get the postgresql-8.1.4-1.zip file. -- Postgresql php tutorials http://www.designmagick.com/ ---(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] allow LIMIT in UPDATE and DELETE
On 5/19/06, Csaba Nagy [EMAIL PROTECTED] wrote: Hi all, Currently the LIMIT clause is not allowed in UPDATE or DELETE statements. I wonder how easy it would be to allow it, and what people think about it ? For our application it would help a lot when processing things chunk-wise to avoid long running queries. I asked that question a while ago.. http://archives.postgresql.org/pgsql-general/2005-04/msg00386.php and got this response: http://archives.postgresql.org/pgsql-general/2005-04/msg00387.php Works quite well :) -- Postgresql php tutorials http://www.designmagick.com/ ---(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] Connecting to PostgreSQL on Linux with windows
On 10 May 2006 07:46:01 -0700, mmaclennan [EMAIL PROTECTED] wrote: Hi, I have a linux box (Fedora 3) running the latest version of PostgreSQL and PostGIS. I am trying connect to the database through a windows computer but can't seem to make the connection work. I've configured the pb_hba.config file in the usr directory to as host all all 192.168.1.0/24md5 but it doesn't seem to work. I know these are the correct settings because I installed PostgreSQL on a windows based machine and can access that version no problem with the aformentioned settings. Can a windows machine connect to a Linux version of Postgresql if so what am I doing wrong? What exactly doesn't work ? Do you get an error message? Does it time out? ... -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Creating of User and Database
On 5/11/06, APSC, Patrick Chee Seng Onn [EMAIL PROTECTED] wrote: Hi, I've just installed postgresql onto my system but unable to successfully add new users and databases. I would receive a error message: createuser: could not connect to database template1: FATAL user root does not exist createdb: could not connect to database template1: FATAL user root does not exist what is the error I am facing anyway? Either try: createuser -U postgres new_username or, su to postgres: su - postgres and try again: createuser new_username shameless plug http://www.designmagick.com/article/5/Starting-Out/PostgreSQL-Users http://www.designmagick.com/article/6/Starting-Out/PostgreSQL-Databases -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Debugging SQL queries
On 5/11/06, Nikolay Samokhvalov [EMAIL PROTECTED] wrote: From my point of view, more important problem is that log doesn't help to find the query (in other words, log message doesn't show context) Yes it does.. but it depends on your logging setup. in psql: test=# blah; ERROR: syntax error at or near blah at character 1 my log shows: ... [9-1] LOG: statement: blah; ... [10-1] ERROR: syntax error at or near blah at character 1 Do you have: log_statement = true in your postgresql.conf ? -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] understanding explain data
On 5/10/06, Alban Hertroys [EMAIL PROTECTED] wrote: Sim Zacks wrote: Something such as: with this explain data, adding an index on table tbl column A would drastically improve the efficiency. Or at least an application that would say, the least efficient part of your query is on this part of the code so that you could more easily figure out what to do about it. The latter part is the most useful IMO, optimizing usually needs a (human) brain to put things into the right perspective. Adding an index can speed up your queries only so much, a more optimal data presentation (like moving calculations to insert/update instead of select) can do a lot more sometimes. It looks like something like that shouldn't be too hard to write... Maybe it even does exist already. Personally I'd prefer a command line tool ;) It would help if you can pipe the output of explain analyze to an external tool from within psql. I've thought about writing a similar tool.. I'm about 30% of the way :) It's written in python and can grab the queries out of the db logs.. but the harder part is working out the explain output.. also taking in to consideration an index might be available but not the best option for the query. I guess the easiest way to check is to have the script turn seq scans off when it runs explain and go from there. If anyone's interested in helping it go further contact me off list (can put it on pgfoundry.org and go from there if need be). -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] simple md5 authentication problems
On 5 May 2006 02:22:32 -0700, robert [EMAIL PROTECTED] wrote: Hi all, hope this is the right list. I have postgres 8.1 running on linux. We have tests that mostly run on windows. I want to run these tests on linux. On these windows boxes, pg_hba.conf has just one line: hostall all 127.0.0.1/32 md5 They use 'postgres' as the user and password to connect to a db. I couldn't start postgres on linux with just that line, so on linux I have: # local is for Unix domain socket connections only local all all ident sameuser # IPv4 local connections: hostall all 127.0.0.1/32 md5 # IPv6 local connections: hostall all ::1/128 ident sameuser I created my db as: postgres=# CREATE DATABASE maragato_test OWNER postgres; I seem to have a user 'postgres' - I'm using the default. postgres=# SELECT * FROM pg_user; usename | usesysid | usecreatedb | usesuper | usecatupd | passwd | valuntil | useconfig --+--+-+--+---+--+--+--- postgres | 10 | t | t| t | | | However, I get this error: /home/postgres psql -h localhost maragato_test postgres Password for user postgres: psql: FATAL: autenticação do tipo password falhou para usuário postgres Sorry - couldn't get local en_US working. That translates to: Authentication of type password failed for user postgres. I think that means 'ident password' . I tried to connect with java and I get the same error. I just need to connect to db 'maragato_test' on local host using 'postgres´ as the user and password, using md5. Try '-h 127.0.0.1' rather than 'localhost' - it's still seeing the connection as coming through the socket, not through tcpip, so it's matching the ident rule. -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Catch individual exceptions
On 3 May 2006 19:16:17 -0700, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: hi all, i want to capture individual sqlexceptions and for each different exception i want to display a different message to the user. the problem is how should i capture from the exception of its type-content and then display a customised message. Wouldn't the programming language you're using handle this? (Which language is it?) -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Authentication connection problems
Here's the deal. For example, when trying to connect to an existing databas, or even creating a new one with the command createdb [dbname], different error occurs. What errors exactly? We can't guess.. Another example: when running the command psql, entering the password and hitting enter, the following error message occur: psql: FATAL: password authentication failed for user Christo Unless you created the user as Christo you should use christo - postgresql is case insensitive unless you put it in quotes. Similarly, when testing the TCP/IP connection with the command psql -U DATABASENAME -W -h localhost the following message shows up psql: FATAL: database dspace does not exist -U is for username, not database name. Since you're not specifying a database name, it will try to use the same as the username (which you're passing in incorrectly). So that's the same as: psql -U dbname -W -h localhost dbname which is probably not what you want. -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Authentication connection problems
On 5/1/06, chris smith [EMAIL PROTECTED] wrote: Here's the deal. For example, when trying to connect to an existing databas, or even creating a new one with the command createdb [dbname], different error occurs. What errors exactly? We can't guess.. Another example: when running the command psql, entering the password and hitting enter, the following error message occur: psql: FATAL: password authentication failed for user Christo Unless you created the user as Christo you should use christo - postgresql is case insensitive unless you put it in quotes. badly worded response there. unless you put quotes around it, postgres turns it lowercase. so Christo becomes christo ... -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Is PostgreSQL an easy choice for a large CMS?
On 4/30/06, Tony Lausin [EMAIL PROTECTED] wrote: Hello all, I'm working on a CMS which requires an open source database capable of handling hundreds of thousands of users simultaneously, with a high rate of database writes, and without buckling. We're talking somewhere between nerve.com/catch27.com and xanga.com/friendster.com PostgreSQL is a personal favorite of mine, and my gut instinct is that it's the best choice for a large scale CMS serving many users; however, I'm getting antsy. I keep getting suggestions that Postgres is really only suited to small and medium projects, and that I should be looking at MySQL for a large scale database drive site. I'm not really a fan of MySQL, but I'll consider it if it truly is the better choice in this case. I just don't understand how it would be. I'm thinking this is solely in reference to VACUUM. Even with autovacuum suport, I tend to agree there is at least one handicap. I could really use some enlightenment on just where PostgreSQL fits in a single-server, highly-trafficked web site serving mostly text, pictures and possibly streaming media. http://people.planetpostgresql.org/xzilla/index.php?/archives/151-Sean-Chittenden-on-RubyOnRails-Podcast.html http://www.postgresql.org/about/casestudies/ http://www.postgresql.org/about/users are all good places to start. TBH it depends a lot on your data and how you structure it. I wrote a small tute on how to get rid of left-join type queries and use triggers to keep count(*) type queries to a minimum.. http://www.designmagick.com/article/36/Forum-Project/Database-Design-Issues It's not always possible, but there are ways to minimize count(*), min(field), max(field) type queries where postgresql isn't able to optimize fully due to mvcc issues. -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Postgres on WinXP - app on Cygwin
On 4/27/06, Tomas Lanczos [EMAIL PROTECTED] wrote: I am curious, whether is it possible to connect to a PostgreSQL database installed on WinXP for an application installed on the same box but running in the Cygwin environment (the app. is the GRASS GIS). There are no reasons why this shouldn't work. Are you having a particular issue with it? -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Problem with complex outer join expression
On 4/26/06, Chris Velevitch [EMAIL PROTECTED] wrote:
I'm using 7.4.5 on win XP Pro SP1.
I'm getting:-
ERROR: syntax error at or near ( at character 155
from the query:-
select dummy_records.sequence_nr,timesheets.weekending,timesheets.timesheet_id
from dummy_records
,left outer join timesheets
on (timesheets.weekending = ('2006-04-09' + (integer
dummy_records.sequence_nr-1)*7)))
where dummy_records.sequence_nr between 1 and (date '2006-04-23' -
date '2006-04-09')/7+1;
What this query is trying to achieve is:-
Find all weekending dates between 2 given weekending dates and any
corresponding timesheets for those weekending dates.
Table joins can only be done against another table field, I don't
think you can do it using an expression like this. That should all be
in the where clause.
What do the timesheets and dummy_records tables look like?
--
Postgresql php tutorials
http://www.designmagick.com/
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL 8.1 + PHP5.1.x/4.4.2 + Apache 2.0.55/1.3.34 PROBLEM!! PLEASE HELP
On 4/27/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
Help! I was trying to make an installation of PHP 5.1.2 + Apache
2.0.55 + PostgreSQL 8.1 on Windows 2003 Server, and I'm stuck. I
got PHP to work with Apache quite smoothly, so did I marry PHP with
PostgreSQL - scripts connecting to the database work fine from
windows command line, except that the following code:
if (extension_loaded(php_pgsql)) {
echo PGSQL loaded!;
}
returns no message. Still database queries work fine. Trouble starts
when I try to open a page in my browser - then I get an error
message like this:
Error: call to udefined function pg_connect()...
I changed the php.ini file a billion times, trying to figure out
what to set in the extension_dir and extension=php_pgsql.dll
lines, and I tried at least as many times to change apache's
httpd.conf file so that the php module is loaded properly. And it
is, as far as I'm concerned - the phpinfo() page shows without a
problem. One peculiar thing about it is that in the Loaded
modules section (don't remember the exact name) there's absolutely
no sign of the pgsql module.
you could change the first check to:
if (!function_exists('pg_connect')) {
die(no pg_connect);
}
What does:
print_r(get_loaded_extensions());
show?
Anything regarding pgsql?
Does your server keep logs? Maybe something in there will give you
some ideas about what's going on..
--
Postgresql php tutorials
http://www.designmagick.com/
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] How to implement a subordinate database?
On 4/19/06, Kynn Jones [EMAIL PROTECTED] wrote: I keep bumping against this situation: I have a main database A, and I want to implement a database B, that is distinct from A, but subordinate to it, meaning that it refers to data in A, but not vice versa. I don't simply want to add new tables to A to implement B, because this unnecessarily clutters A's schema with tables that entirely extraneous to it. Hmm. Postgres supports table inheritance, but I don't think it supports schema or database inheritance in the way you want it to. http://www.postgresql.org/docs/8.1/interactive/tutorial-inheritance.html http://www.postgresql.org/docs/8.1/interactive/ddl-inherit.html -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] hard shutdown of system
On 4/17/06, surabhi.ahuja [EMAIL PROTECTED] wrote:
the user tries to do kill -9 -1 and log in again
in the startup script i do the following
/sbin/pidof -s postmaster
and it still displays some value,
however ps -aef | grep postmaster does not display anything
is it ok if i do the following
pid1=`/sbin/pidof -s postmaster`
pid2=`ps -eaf | grep postmaster | grep -v grep | tail -1 | awk '{print
$2}'`
if ($pid1 and $pid2)
= postmaster is already running
otherwise
i check if postmaster.pid exists
if it does, i delete it
and then start postmaster by doing $PGCTL -l $POSTGRES_LOG -D $PGDATA -p
$POSTMASTER -o '-p ${PGPORT}' start /dev/null 21
Check out the startup script. Depending on what system you are
running, this might already all be taken care of.
Here's a mandrake example (I think the redhat version is pretty similar).
http://techdocs.postgresql.org/scripts/mandrake72-startup
--
Postgresql php tutorials
http://www.designmagick.com/
---(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] Comparing text field
On 13 Apr 2006 12:20:08 -0700, Nik [EMAIL PROTECTED] wrote: I am trying to compare a large string (that has new line characters in it) to the contents of the text field (which also has new line characters in it) and it is not behaving as expected. For example I have the following record in the database: id=83 message=VAC153-683-685-131830- /O.NEW.KLWX.SV.W.0022.060413T1742Z-060413T1830Z/ BULLETIN - EAS ACTIVATION REQUESTED SEVERE THUNDERSTORM WARNING NATIONAL WEATHER SERVICE BALTIMORE MD/WASHINGTON DC 142 PM EDT THU APR 13 2006 THE NATIONAL WEATHER SERVICE IN STERLING VIRGINIA HAS ISSUED A * SEVERE THUNDERSTORM WARNING If I do the following query SELECT id FROM table1 WHERE message='VAC153-683-685-131830- /O.NEW.KLWX.SV.W.0022.060413T1742Z-060413T1830Z/ BULLETIN - EAS ACTIVATION REQUESTED SEVERE THUNDERSTORM WARNING NATIONAL WEATHER SERVICE BALTIMORE MD/WASHINGTON DC 142 PM EDT THU APR 13 2006 THE NATIONAL WEATHER SERVICE IN STERLING VIRGINIA HAS ISSUED A * SEVERE THUNDERSTORM WARNING' I get no results back, even though the message is equivalent. How should I perform this comparison so that the above query returns id=83? Could one have \r\n and the other have \n ? Are you doing the comparison in psql or through a language (php, ruby, python, other) ? -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] posting request
On 4/13/06, Anton Andreev [EMAIL PROTECTED] wrote: I want to post some questions. Go ahead and post them :) We don't bite :) -- Postgresql php tutorials http://www.designmagick.com/ ---(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] the integer type
I have only recently started to use Postgresql and have a problem. I am using v8.1 on windows. I cannot seem to get the DB to accept either INT or INTEGER as a type when using the admin tool. Only INT2 or INT4 work. I even downloaded Navicat trial and this has the same issue. Is there a setup required to allow these standard types to be enabled? It's a built in type so you don't need to do anything. So something like this: create table t1(a int); fails? What message do you get? -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] the integer type
On 4/14/06, Zahir Lalani [EMAIL PROTECTED] wrote: Hi Chris I am using pgAdmin III. Created a table, then started adding fields. This is where the problem hits - the drop down does not give you an INTEGER option, so you cannot shoose it. Also I tried creating a script in the query view and running it, but again, if I used INT or INTEGER it errors. Change it INT4 and its fine. What error do you get? I'm sure pgAdmin will show something. Try it from console: psql.exe dbname create table t1(a int); (Always CC the list, someone else might be able to help you if I don't know the answer). -Original Message- From: chris smith [mailto:[EMAIL PROTECTED] Sent: 13 April 2006 15:17 To: Zahir Lalani Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] the integer type I have only recently started to use Postgresql and have a problem. I am using v8.1 on windows. I cannot seem to get the DB to accept either INT or INTEGER as a type when using the admin tool. Only INT2 or INT4 work. I even downloaded Navicat trial and this has the same issue. Is there a setup required to allow these standard types to be enabled? It's a built in type so you don't need to do anything. So something like this: create table t1(a int); fails? What message do you get? -- Postgresql php tutorials http://www.designmagick.com/ __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Regex with patterns in table field
On 4/14/06, pgdb [EMAIL PROTECTED] wrote: Hi Andreas, strange but I don't see html from my original email received from the mailing list, hope this reply is ok:) If I'm not wrong, the example you've provided is trying to return matching rows from multiple patterns and texts as inputs in the regex search. The text in regular expression have to be just a single string(with no white character) for my case. Matching should then be done with patterns from each row, returning the row(s) that contain matching regex pattern(s). Appreciate any advice. Thanks. This page might be what you're after: http://www.postgresql.org/docs/8.1/static/functions-matching.html -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] pgcrypto-crypt
On 4/6/06, AKHILESH GUPTA [EMAIL PROTECTED] wrote: dear all, i want to encrypt and decrypt one of the fields in my table (i.e-password field) i have searched and with the help of pgcrypto package, using function crypt, i am able to encrypt my data, but there is nothing which i found to decrypt that same data, plz anybody give me the function to decrypt that encrypted value. The crypt function can't be decrypted (whether it's in postgresql or anywhere else). Crypt is meant to be used for passwords and such that you don't need to reverse (you only compare against). -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] what is this error
On 4/3/06, venu gopal [EMAIL PROTECTED] wrote: Dear List, I have created a database called dhis13 and created a login role with user name venu and pwd gis now i have created an sql file using shp2pgsql command it worked successfully when i m importing the same to the dhis13 database i was giving with the following error all the database and loginroles is created at pgadminIII (not at command prompt) When i try to import i was getting the following error C:\Program Files\PostgreSQL\8.1\binpsql -U venu -d dhis13 -f e:\venu\postgischi ttoorshp.sql Password for user venu: BEGIN psql:e:/venu/postgischittoorshp.sql:2: NOTICE: CREATE TABLE will create implici t sequence chittoor_ver0_gid_seq for serial column chittoor_ver0.gid psql:e:/venu/postgischittoorshp.sql:2: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index chittoor_ver0_pkey for table chittoor_ver0 CREATE TABLE psql:e:/venu/postgischittoorshp.sql:3: ERROR: function addgeometrycolumn(unkno wn, unknown, unknown, unknown, unknown, integer) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. You're missing a function called addgeometrycolumn. It's not a native postgres function (as far as I'm aware) - you'll have to find where it comes from and import it. -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] encryption/decryption
On 4/1/06, AKHILESH GUPTA [EMAIL PROTECTED] wrote: hi all, just to ask u all one thing regarding encryption/decryption. i am migrating my database from mysql to pgSQL using a php script. in mysql there is a table 'users' where a field 'password' is there of type varchar(30), and is encrypted using AES algorithm The 'users' table in the 'mysql' database is md5'ed. You can't decrypt it. -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] How to use result column names in having cause
On 3/31/06, Andrus [EMAIL PROTECTED] wrote: CREATE TEMP TABLE foo( bar integer ); SELECT 123 AS x FROM foo GROUP BY 1 HAVING x AVG(bar) causes ERROR: column x does not exist Why ? How to make this working ? In real application I have long expression instead of 123 and do'nt want repeat this expression in HAVING clause. You have to repeat the expression. AS changes the output name, it can't be used either in the where clause or any other limiting factor like 'having': test=# create table t1(a int); test=# insert into t1(a) values (1); test=# SELECT a AS x from t1 where x=1; ERROR: column x does not exist -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] How to use result column names in having cause
On 3/31/06, Andrus [EMAIL PROTECTED] wrote: In real application I have long expression instead of 123 and do'nt want repeat this expression in HAVING clause. You have to repeat the expression. AS changes the output name, it can't be used either in the where clause or any other limiting factor like 'having': Doc about HAVING condition says: Each column referenced in condition must unambiguously reference a grouping colum HAVING x AVG(bar) unambiguously references to a grouping column x Is this bug ? It is very tedious to repeat same column expression in a multiple times: one time in column expression, and n times in having clause. But you're not referencing x, you're trying to use AVG(bar) in your expression. I assume it's this way because the standard says so.. one of the more knowledgable list members will be able to confirm/deny this. -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] giving users access to specific databases
On 4/1/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, I'm hoping someone can tell me how to go about this, or if a solution is even possible with my current set up. I realize this question may go beyond pure postgres topics and have to do more with how my hosting company has their servers configures, but this group seemed like my best option for help. If anyone has suggestions on what other groups might be helpful to post this question to, I would really appreciate it. I do my database application work on a shared Linux server provided by my hosting company. It appears they have just one installation of postgres on the server and that all of their customers on that server are able to create databases and users under their account. I'm not quite sure how they have enabled specific user accounts for access to my specific part of the server, but I do know that when I log in I have access to a cpanel interface, email configuration, all of my files on the server, access to create new postgres databases and a link to phpPgAdmin. Do you get the option to create a new database user? You could create a new user and give that user access to your database. -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] about partitioning
On 4/1/06, fufay [EMAIL PROTECTED] wrote: dear all, i created a master table and a sub table which inherits the main table. and then i made a trigger and a function that want to keep the master table empty. but the trigger didn't work anyway.when i inserted data into the table news,both the master table and the sub table were inserted. why? i just want the empty master table,any good ideas? lots of thanks for all. here r DDls: - --master table； CREATE TABLE public.news ( id SERIAL, title VARCHAR(100) NOT NULL, content VARCHAR NOT NULL, author VARCHAR(50) NOT NULL, date DATE DEFAULT now(), CONSTRAINT news_pkey PRIMARY KEY(id) )WITHOUT OIDS; --rule； CREATE RULE news_current_partition AS ON INSERT TO public.news DO INSTEAD (INSERT INTO news_001 (title, content, author) VALUES (new.title, new.content, new.author)); --trigger； CREATE TRIGGER news_triggers BEFORE INSERT ON public.news FOR EACH ROW EXECUTE PROCEDURE public.deny_insert(); --function； CREATE OR REPLACE FUNCTION public.deny_insert () RETURNS trigger AS $body$ BEGIN RETURN NULL; END; $body$ LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER; --sub table； CREATE TABLE public.news_001 ( CONSTRAINT news_001_date_check CHECK ((date = '2006-03-29'::date) AND (date '2006-04-28'::date)) ) INHERITS (public.news) WITHOUT OIDS; Since the fields don't exist in news_001, it has to store them somewhere - in the table it inherits from. Inheritence is meant to be used to change something in the substructure/child table/whatever. If that object isn't in the child, it has to go back to the parent to work out what to do (in your case, store the entry). -- Postgresql php tutorials http://www.designmagick.com/ ---(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] about un-discrible
On 3/29/06, 查海平 [EMAIL PROTECTED] wrote: hi, Could anyone tell me how to un-discrible this mails list? how to do? Check the mail headers: List-Unsubscribe: mailto:[EMAIL PROTECTED] -- Postgresql php tutorials http://www.designmagick.com/ ---(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] inheridt tables from db
On 3/25/06, nik600 [EMAIL PROTECTED] wrote: hi i am considering to port an important web applications to postgres, this applications is made of php and i reuse the same code for many customer, due to have a clean structure and simple updates... now i have one code and many databases in mysql... i know that with postgres i can inheridt some properties...can i have a main database, called A and then many databases, Customer1, Customer2, Customer3 and if i made a change in A the changes is replicated to Customer1,2 and 3? Databases don't have inherited properties, but tables do. http://www.postgresql.org/docs/8.1/static/tutorial-inheritance.html http://www.postgresql.org/docs/8.1/static/ddl-inherit.html -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pg 8.1.2 performance issue
On 3/26/06, Ed L. [EMAIL PROTECTED] wrote: On Saturday March 25 2006 9:36 pm, Ed L. wrote: I have a performance riddle, hoping someone can point me in a helpful direction. We have a pg 8.1.2 cluster using Apache::Sessions and experiencing simple UPDATEs taking sometimes 30+ seconds to do a very simply update, no foreign keys, no triggers: Table public.sessions Column | Type | Modifiers ---+---+--- id| character(32) | not null a_session | text | Indexes: sessions_pkey PRIMARY KEY, btree (id) The table has 6800 rows over 18000 pages, and is getting a minimum of many tens of thousands of updates per day with queries like this: If you're updating that much, how often are you running 'analyze'? Are you running autovacuum? How often? -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Practical limit on number of tables ina single database
On 3/24/06, Just Someone [EMAIL PROTECTED] wrote: Hi, I am creating a hosted solution that I want to base on separation by schemas. So that each hosted family we will have, will have a schema assigned to it (and a user). On login I will set the search path, and so each family will see it's tables. This is all tested and works fine. But I would like to know if there's a practical limit to the number of schemas and tables I can have. Please note that I'm using table spaces to make sure the directories are manageable. I tested it so far with 13000 schemas and users, with 26 tables in each schema (a total of more that 33 tables). It works perfectly, but I would like to know if someone has experience with this number of tables/schemas, and if there's a limit I should be careful of. There's no real limit. See this recent thread for details: http://archives.postgresql.org/pgsql-advocacy/2006-03/msg00082.php and http://people.planetpostgresql.org/greg/index.php?/archives/37-The-million-table-challenge.html -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] can't create user collumn
On 3/17/06, loki [EMAIL PROTECTED] wrote: Hi, i'm just starting with postgres DB, but this looks very strange to me: If i try to create table with collumn user, it fails with error: create exec error:ERROR: syntax error at or near user at character 368 query: CREATE TABLE Log ( log_datedateNOT NULL, log_timetimeNOT NULL, timezoneint NOT NULL default 60 , destvarchar(20) NOT NULL default 'messages', hostnamevarchar(100)NOT NULL default 'localhost', source varchar(20) NOT NULL default 'db_speed', pid numeric(10) NOT NULL default 0 , categoryint NOT NULL default 10 , priorityint NOT NULL default 10 , userint NOT NULL default 0 , log varchar(800)NOT NULL ); But if I change the collumn name to usr (just this, nothing else), it is o.k. and the table is created. user is a reserved sql word: http://www.postgresql.org/docs/8.1/static/sql-keywords-appendix.html -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] full text indexing
Hi all, Just wondering which full text module is better what the differences are between tsearch and fti ? The table in question has roughly 80,000 rows. Thanks! -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] out of memory using Postgres with Spring/Hibernate/Java
On 3/15/06, maarten roosendaal [EMAIL PROTECTED] wrote: Hi, We are currently having a problem that our Postgres DB is throwing an SQL error which states that it's 'out of memory'. What we have is a DB with 1 table that has 3.9 million records. We need to find certain records that are to be processed by a Java App so we do a select id from table where type=a and condition in (1, 2) order by id limit 2000. When this query gets executed we see the memory on the DB Server increasing and after it has finishes it drops a bit but we see it growing a few MB per few minutes. This has caused an out of memory after the system has been processing for a day or 2. The query is heavy because of the order by but that does not explain why the memory is increasing. What does explain show for the query? Are the fields indexed appropriately? Have you analyzed the table recently? Postgres needs to store the ordered results somewhere so of course that explains the memory increase. -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Question about index usage
On 3/7/06, Guido Neitzer [EMAIL PROTECTED] wrote:
Hi.
Is there a reason why this query:
select id from dga_dienstleister where plz in ('45257', '45259');
doesn't use this index:
dga_dienstleister_plz_index btree (plz varchar_pattern_ops)
but uses this index:
dga_dienstleister_plz_index2 btree (plz)
I had the first index setup for queries with plz like '4525%' but I
never tested the in query until I saw in the logs that these
queries where slow compared to the rest. Query plans at the end.
cug
DGADB=# explain analyse select id from dga_dienstleister where plz
like
'45257'; Q
UERY PLAN
Bitmap Heap Scan on dga_dienstleister (cost=2.07..82.41 rows=21
width=8) (actual time=13.489..14.211 rows=16 loops=1)
Filter: ((plz)::text ~~ '45257'::text)
- Bitmap Index Scan on dga_dienstleister_plz_index
(cost=0.00..2.07 rows=21 width=0) (actual time=13.323..13.323 rows=16
loops=1)
Index Cond: ((plz)::text ~=~ '45257'::character varying)
Total runtime: 14.328 ms
(5 rows)
DGADB=# explain analyse select id from dga_dienstleister where plz =
'45257';
QUERY
PLAN
---
Bitmap Heap Scan on dga_dienstleister (cost=2.07..82.41 rows=21
width=8) (actual time=0.486..0.663 rows=16 loops=1)
Recheck Cond: ((plz)::text = '45257'::text)
- Bitmap Index Scan on dga_dienstleister_plz_index2
(cost=0.00..2.07 rows=21 width=0) (actual time=0.424..0.424 rows=16
loops=1)
Index Cond: ((plz)::text = '45257'::text)
Total runtime: 0.826 ms
(5 rows)
Try without the quotes:
select id from dga_dienstleister where plz in (45257, 45259);
What is the table structure for dga_dienstleister ?
--
Postgresql php tutorials
http://www.designmagick.com/
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] User tables
Suppose there 3 users red, green, blue. How can the user green know what tables he has created?! From psql command line \dt lists every table in the DB!!! Thanks in advance. Hrishi If you mean that the owner of the table(s) is the user green, then try select * from pg_tables where tableowner='green'; or from inside psql: \z ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Question about the contrib rpm ?
On 3/3/06, Agnes Bocchino [EMAIL PROTECTED] wrote: Hello Tom, hello List, Sorry if we haven't been clear in our first mail. We don't really understand your answer. So, we try to clarify our general question and give more details : When we go on the web site to download PostgreSQL 8.1.2, we find not only the serveur rpm but also some others rpms. and we don't kow which of them we have to install together with our rpm make from the 8.1.2 targz. We are making our rpm on Novascale Ia64 We have used the postgresql-8.1.2.tar.gz file downloaded from the PostGreSQL web site. From that file, we have re-compiled PostGreSQL for IA64 on Red Hat Enterprise Linux 4 AS, with the icc Intel compiler. We would like to 'deliver' a more complete set as possible. and we don't know if we have to package some others packages.. For the langage python,perl,tcl ...we know that if we need them we have to use the --with option when we compile. It seems also to us that it is not necessary to have the lib rpm as the necessary librairies are include in the rpm when wecompile and package it. but ..we dont' know what doing with the *contrib *rpm available on the net, should we have to deliver it with our rpm. Why this question ? When we have extract files from the archive file, we have obtained these directories : [/BUILD/postgresql-8.1.2]$ ls -ltr total 1528 -rw-r--r-- 1 postdev pgsql445 Apr 23 2004 aclocal.m4 -rw-r--r-- 1 postdev pgsql 1375 Oct 1 2004 README -rw-r--r-- 1 postdev pgsql 1412 Oct 6 2004 Makefile -rw-r--r-- 1 postdev pgsql 1192 Dec 31 2004 COPYRIGHT -rw-r--r-- 1 postdev pgsql 3435 May 1 2005 GNUmakefile.in -rwxr-xr-x 1 postdev pgsql 689752 Jan 5 05:02 configure -rw-r--r-- 1 postdev pgsql 43596 Jan 5 05:02 configure.in -rw-r--r-- 1 postdev pgsql 387774 Jan 6 05:09 HISTORY -rw-r--r-- 1 postdev pgsql 44484 Jan 6 05:09 INSTALL drwxr-xr-x 2 postdev pgsql 4096 Jan 6 05:09 config drwxr-xr-x 35 postdev pgsql 4096 Jan 6 05:09 *contrib* -rw-r--r-- 1 postdev pgsql 3435 Feb 16 12:22 GNUmakefile -rwxr-xr-x 1 postdev pgsql 56658 Feb 16 12:22 config.status drwxr-xr-x 15 postdev pgsql 4096 Feb 16 12:22 src drwxr-xr-x 7 postdev pgsql 4096 Feb 16 12:22 doc -rw-r--r-- 1 postdev pgsql 278305 Feb 16 12:22 config.log Under the contrib repertory, we have among others things *start-scripts* directory which contains the linux file which allows to launch automatically PostGreSQL each time the machine reboots. [ contrib]# ls adddepend dblink intarray mSQL-interface pgstattupletablefunc btree_gist dbmirror isbn_issn oid2name pg_trgmtips chkpassearthdistance lo oracle README tsearch2 contrib-global.mk fulltextindex ltree pgbench seguserlock cube fuzzystrmatch macpg_buffercache spivacuumlo dbase intagg Makefile pgcrypto start-scripts xml2 And when we install our rpm, we don't have a contrib directory such as this obtains after having extracted the files from the tar.gz archive. Our question is how to add in the rpm that we have generated a contrib directory, in order to have scripts like linux ? If you're building your own rpm you'll need to do it yourself - that's getting beyond what this list can help you with. Or should we used the contrib rpm available in the web site No - because it's not built for your system or with your compiler, it may work but you may also run into strange bugs. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Could DBmirror be done with the mechine using RPM installation instaed of souce code [tar.gz] ?
I use the two mechines with FC3 and postgresql 8.1 RPM installation and would like to do DBmirror or other postgresql database replication from master to slave server . Could I do this replication using RPM or I must use the tar.gz version ? Could anybody suggest me about replication? There should be a 'postgresql-contrib' rpm for FC3 (use your favourite search engine). If you can't find one - create an rpm (it's pretty easy). That's getting way outside of this mailing list's scope though. ---(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] Could DBmirror be done with the mechine using RPM installation instaed of souce code [tar.gz] ?
On 2/26/06, Amrit Angsusing [EMAIL PROTECTED] wrote: I try to do it by rpm installation but the seemed to miss some file which was described by the doc. for ex. pending.so and its' path and perl /./DBmirror.pl could not run If you have a problem copy the exact error message, nobody can help you with that message. Could you suggest me the URL how to install DBmirror by the rpm mechine? Google? rpm -ivh /path/to/rpm.rpm That will install the rpm, then you need to set it up. Read the appropriate documentation (ie the docs included with dbmirror) on how to do it. 2006/2/25, chris smith [EMAIL PROTECTED]: I use the two mechines with FC3 and postgresql 8.1 RPM installation and would like to do DBmirror or other postgresql database replication from master to slave server . Could I do this replication using RPM or I must use the tar.gz version ? Could anybody suggest me about replication? There should be a 'postgresql-contrib' rpm for FC3 (use your favourite search engine). If you can't find one - create an rpm (it's pretty easy). That's getting way outside of this mailing list's scope though. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] createuser permssion for group
On 2/26/06, Jebus [EMAIL PROTECTED] wrote: Is it possible to give a group the the createuser permission ? This way if a user in the group they can create users. Version 8.1.x does.. postgres=# \h create group ... | CREATEROLE | NOCREATEROLE | CREATEUSER | NOCREATEUSER ---(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] success with p2p ip connection
Please reply to the same thread you start instead of starting a new one every time (choose the last reply and hit Reply to All). [EMAIL PROTECTED] wrote: I looked at the server machine, in a section regarding ip connections, and saw that security was set to prevent other machines from connecting, so once I set it to no security, I could connect now all I need to do is figure out how to define high security, but allow 192.0.0.101 (the client machine) so... I am quite pleased to see that it works... ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] using limit with delete
Hi all, I'm trying to use a limit clause with delete, but it doesn't work at the moment (are there plans to add this - I could try to do up a patch ?). eg. delete from table where x='1' limit 1000; Is there another way to approach this? I'm trying to delete records through a webapp and if there are 500,000 records for example, I can't really leave the page open and expect it to finish... -- Regards, Chris Smith Unit 2, 3 National Street, Rozelle, NSW 2039 Australia Ph: +61 2 9555 5570 Fx: +61 2 9555 5571 email: [EMAIL PROTECTED] web: http://www.interspire.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] using limit with delete
Hi Csaba, Because I have a where clause limiting which records I'm deleting. I'm deleting old info from a database, so I'm doing: DELETE FROM sessions WHERE EXISTS (SELECT sessiontime FROM sessions WHERE sessiontime (timenow-7days) LIMIT 100) (timenow-7days is evaluated in PHP and made an int). So every time the page gets hit, I'm deleting up to 100 records that are older than 7 days.. Csaba Nagy wrote: Hi Chris, Just a thought: if you have to clear the table anyway, wouldn't it work for you to use truncate ? That should be faster than delete. HTH, Csaba. On Thu, 2005-04-07 at 04:11, Chris Smith wrote: I don't care about the order in my particular case, just that I have to clear the table. I'll try the subquery and see how I go :) Thanks! Neil Conway wrote: Chris Smith wrote: I'm trying to use a limit clause with delete, but it doesn't work at the moment It isn't in the SQL standard, and it would have undefined behavior: the sort order of a result set without ORDER BY is unspecified, so you would have no way to predict which rows DELETE would remove. delete from table where x='1' limit 1000; You could use a subquery to achieve this: DELETE FROM table WHERE x IN (SELECT x FROM table ... ORDER BY ... LIMIT ...); -Neil ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Regards, Chris Smith Unit 2, 3 National Street, Rozelle, NSW 2039 Australia Ph: +61 2 9555 5570 Fx: +61 2 9555 5571 email: [EMAIL PROTECTED] web: http://www.interspire.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] sequence advances on failed insert
Yes, he meant that one should not assume that the next value will be one increment higher than the current highest value in the table. You shouldn't rely on them being sequential because they will not always be that way. Sven Willenberger wrote: David Fetter presumably uttered the following on 04/07/05 20:16: On Thu, Apr 07, 2005 at 07:59:52PM -0400, Matthew Terenzio wrote: I'm noticing that a sequence is advancing even if the insertion fails. Is this weird or expected? It's expected. Sequences are guaranteed to generate unique IDs. These happen to be an increasing sequence of integers, but there is no attempt to make this a gap-free sequence, and your apps should not depend on the actual value of said ID. I assume by not depend on the actual value that one should not assume that the next value will be one increment higher than the current highest value in the table; because it is guaranteed to be unique, I would think it to be an excellent way to assign a customer id, for example, which can then be referenced (foreign key, etc) by other tables after a new record is added. Unless there is some other reason one should not use a sequence value as any type of identifier? Sven ---(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 -- Regards, Chris Smith Unit 2, 3 National Street, Rozelle, NSW 2039 Australia Ph: +61 2 9555 5570 Fx: +61 2 9555 5571 email: [EMAIL PROTECTED] web: http://www.interspire.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] using limit with delete
Hi all, I'm trying to use a limit clause with delete, but it doesn't work at the moment (are there plans to add this - I could try to do up a patch ?). eg. delete from table where x='1' limit 1000; (so truncate is out - I have a 'where' clause). Is there another way to approach this? I'm trying to delete records through a webapp and if there are 500,000 records for example, I can't really leave the page open and expect it to finish... -- Regards, Chris Smith Unit 2, 3 National Street, Rozelle, NSW 2039 Australia Ph: +61 2 9555 5570 Fx: +61 2 9555 5571 email: [EMAIL PROTECTED] web: http://www.interspire.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] using limit with delete
I don't care about the order in my particular case, just that I have to clear the table. I'll try the subquery and see how I go :) Thanks! Neil Conway wrote: Chris Smith wrote: I'm trying to use a limit clause with delete, but it doesn't work at the moment It isn't in the SQL standard, and it would have undefined behavior: the sort order of a result set without ORDER BY is unspecified, so you would have no way to predict which rows DELETE would remove. delete from table where x='1' limit 1000; You could use a subquery to achieve this: DELETE FROM table WHERE x IN (SELECT x FROM table ... ORDER BY ... LIMIT ...); -Neil ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Regards, Chris Smith Unit 2, 3 National Street, Rozelle, NSW 2039 Australia Ph: +61 2 9555 5570 Fx: +61 2 9555 5571 email: [EMAIL PROTECTED] web: http://www.interspire.com ---(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] using limit with delete
For the archives... Using 7.4 so IN() is a little slower, so I rewrote it slightly to be DELETE FROM table WHERE EXISTS (select x from table LIMIT ...); Works very nicely :) Thanks again. Neil Conway wrote: Chris Smith wrote: I'm trying to use a limit clause with delete, but it doesn't work at the moment It isn't in the SQL standard, and it would have undefined behavior: the sort order of a result set without ORDER BY is unspecified, so you would have no way to predict which rows DELETE would remove. delete from table where x='1' limit 1000; You could use a subquery to achieve this: DELETE FROM table WHERE x IN (SELECT x FROM table ... ORDER BY ... LIMIT ...); -Neil ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Regards, Chris Smith Unit 2, 3 National Street, Rozelle, NSW 2039 Australia Ph: +61 2 9555 5570 Fx: +61 2 9555 5571 email: [EMAIL PROTECTED] web: http://www.interspire.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Referencing created tables fails with message that
Try putting quotes around the table name: select * from Table; ? Do you see it in the table list? \dt Tommy Svensson wrote: I have just installed Postgresql and tried it for the first time. One very serious problem I ran into was when actually trying to use created tables. Creating a simple table without any foreign keys works OK, but after creating the table it is not possible to do a select on it! I tried the following variants: SELECT * FROM table; SELECT * FROM public.table; SELECT * FROM schema.public.table; All result in the message The relation table does not exist! or The relation public.table does not exist!. Creating a new table with a foreign key referencing the first table is also impossible due to exactly the same error message! This behaviour is the same using DBVisualizer/jdbc or psql. So the question is how do you actually reference the tables you have created so that postgres will find them ? The tables do actually get created. I can se them in DBVisualizer. I'm using version 7.4.5 on Linux Mandrake 10.1. Best Regards, Tommy Svensson ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Regards, Chris Smith Suite 30, 45-51 Huntley St, Alexandria, NSW 2015 Australia Ph: +61 2 9517 2505 Fx: +61 2 9517 1915 email: [EMAIL PROTECTED] web: www.interspire.com ---(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] Easy transaction question
You can issue them one at a time - however you can't have a transaction that spans multiple pages (ie you can't start it on index.php and finish it on end.php). Rick Schumeyer wrote: A question about using transactions from php: Does the entire transaction have to be sent all at once, or can I begin the transaction, issue commands one at a time, and then end the transaction? -- Regards, Chris Smith Suite 30, 45-51 Huntley St, Alexandria, NSW 2015 Australia Ph: +61 2 9517 2505 Fx: +61 2 9517 1915 email: [EMAIL PROTECTED] web: www.interspire.com ---(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] MD5
Use the md5 function:
select md5('welcome');
md5
--
40be4e59b9a2a2b5dffb918c0e86b3d7
(1 row)
Regards,
Chris Smith
Suite 30, 45-51 Huntley St, Alexandria, NSW 2015 Australia
Ph: +61 2 9517 2505
Fx: +61 2 9517 1915
email: [EMAIL PROTECTED]
web: www.interspire.com
Jamie Deppeler wrote:
Tino Wildenhain wrote:
Hi,
Am Freitag, den 17.12.2004, 09:41 +1100 schrieb Jamie Deppeler:
Hi,
I was just wondering is it possible to encrypt a filed in the database
with md5? i know it is possible to do it with DB users
No. You cannot encrypt with md5 because you cant decrypt.
md5 is a hash function. But you can use it any time to
hash values you insert.
Regards
Tino
Well basically i want to store and hashed value that will never be
changed just compaired too hashed values
also when i try to encrpyt a field i get this error
encypt(text,unknown,unknown)
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.296 / Virus Database: 265.5.4 - Release Date: 12/15/2004
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.296 / Virus Database: 265.5.4 - Release Date: 12/15/2004
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] MD5
(Ignore my other reply.. ;P) You'll need to import the pgcrypto.sql file (this creates the functions for you). Read the doco on how to install the extension, it should tell you where the sql file is. Regards, Chris Smith Suite 30, 45-51 Huntley St, Alexandria, NSW 2015 Australia Ph: +61 2 9517 2505 Fx: +61 2 9517 1915 email: [EMAIL PROTECTED] web: www.interspire.com Jamie Deppeler wrote: Tino Wildenhain wrote: Hi, Am Freitag, den 17.12.2004, 09:41 +1100 schrieb Jamie Deppeler: Hi, I was just wondering is it possible to encrypt a filed in the database with md5? i know it is possible to do it with DB users No. You cannot encrypt with md5 because you cant decrypt. md5 is a hash function. But you can use it any time to hash values you insert. Regards Tino Well basically i want to store and hashed value that will never be changed just compaired too hashed values also when i try to encrpyt a field i get this error encypt(text,unknown,unknown) No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.296 / Virus Database: 265.5.4 - Release Date: 12/15/2004 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.296 / Virus Database: 265.5.4 - Release Date: 12/15/2004 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Debian Packages for Postgresql 8.0.0 RC1
I doubt you'll find any for an RC release... when 8 is released (final), then I think you'll find them. Regards, Chris Smith Suite 30, 45-51 Huntley St, Alexandria, NSW 2015 Australia Ph: +61 2 9517 2505 Fx: +61 2 9517 1915 email: [EMAIL PROTECTED] web: www.interspire.com Simon Wittber wrote: Despite 30 minutes of googling, I am unable to find any debian packages for Postgresql 8.0.0 RC1. Does anyone know where I might obtain them? Sw. ---(end of broadcast)--- TIP 8: explain analyze is your friend -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.296 / Virus Database: 265.5.4 - Release Date: 12/15/2004 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] sorting problem
Would doing it this way require an index: create index lower_lastname on table x lower(lastname); ? Regards, Chris Smith Suite 30, 45-51 Huntley St, Alexandria, NSW 2015 Australia Ph: +61 2 9517 2505 Fx: +61 2 9517 1915 email: [EMAIL PROTECTED] web: www.interspire.com Michael Fuhr wrote: On Fri, Dec 17, 2004 at 11:28:36AM +1100, Jamie Deppeler wrote: Problem i am having at the moment i cant get a true alpha sort to work as Order By is sorting A..Z then a..z where i need aA..zZ sort independant of case. ORDER BY LOWER(person.lastname) or ORDER BY UPPER(person.lastname) -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.296 / Virus Database: 265.5.4 - Release Date: 12/15/2004 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Insert do not work in my case
The easiest way to start is to turn on query logs for your server and see if it gets that far. If it does - copy the query into psql and run it manually - it will tell you if there's a problem. If it doesn't get that far, then that's another problem altogether... Regards, Chris Smith Suite 30, 45-51 Huntley St, Alexandria, NSW 2015 Australia Ph: +61 2 9517 2505 Fx: +61 2 9517 1915 email: [EMAIL PROTECTED] web: www.interspire.com Mickael Remond wrote: Hello, I am running an application on Postgresql 8.0.0 rc1. The application is running on JBoss 4.0.1RC2 and I am using the JDBC driver pg80b1.308.jdbc3.jar. My problem is that insert that are sent to the database through a prepared statement are not written in the database. The application is doing a subsequent read and fail. A simple select from psql show that the table is stil empty. The log in debug level mode 3 are showing no particular problem. I clearly see the prepared statement (Not complete but with question marks instead of variable). I then see the select but the read is failing in the application. I really do not see why this is happening. I suspect a configuration problem but does not see what I should change. The application was working properly on PostgreSQL 7.2. Do you have any clue ? -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.296 / Virus Database: 265.5.4 - Release Date: 12/15/2004 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] postgresql and javascript
[EMAIL PROTECTED] wrote: Does anyone know how to connect javascript to a postgresql database You can't connect javascript to any sort of database. You need something like php, python etc - it can connect to your database and generate javascript. Regards, Chris Smith Suite 30, 45-51 Huntley St, Alexandria, NSW 2015 Australia Ph: +61 2 9517 2505 Fx: +61 2 9517 1915 email: [EMAIL PROTECTED] web: www.interspire.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] rewrite count distinct query
Hi all, 'Scuse the long post :) I'm trying to include all relevant info.. I'm trying to work out a better way to approach a query, any tips are greatly appreciated. The relevant tables: db=# \d tp_conversions Table public.tp_conversions Column | Type | Modifiers ---++ conversionid | integer| not null default 0 type | character varying(10) | name | character varying(255) | amount| double precision | cookieid | character varying(32) | currtime | integer| ip| character varying(20) | origintype| character varying(20) | originfrom| character varying(255) | origindetails | character varying(255) | userid| integer| Indexes: tp_conversions_pkey primary key, btree (conversionid) conv_origindetails btree (origindetails) conv_originfrom btree (originfrom) conv_origintype btree (origintype) conv_time btree (currtime) conv_userid btree (userid) trackpoint=# SELECT count(*) from tp_conversions; count --- 261 (1 row) db=# \d tp_search Table public.tp_search Column | Type | Modifiers --++ searchid | integer| not null default 0 searchenginename | character varying(255) | keywords | character varying(255) | currtime | integer| ip | character varying(20) | landingpage | character varying(255) | cookieid | character varying(32) | userid | integer| Indexes: tp_search_pkey primary key, btree (searchid) search_cookieid btree (cookieid) search_keywords btree (keywords) search_searchenginename btree (searchenginename) search_userid btree (userid) trackpoint=# SELECT count(*) from tp_search; count --- 5086 (1 row) What I'm trying to do... Work out the number of conversions for each search origin. This query works: select count(distinct conversionid) as convcount, count(distinct searchid) as searchcount, (count(distinct conversionid) / count(distinct searchid)) as perc, s.searchenginename from tp_conversions c, tp_search s where c.origintype='search' and s.searchenginename=c.originfrom and s.userid=c.userid and c.userid=1 group by searchenginename order by convcount desc; convcount | searchcount | perc | searchenginename ---+-+--+-- 15 |2884 |0 | Google 1 | 110 |0 | Google AU 2 | 308 |0 | Google CA 1 | 25 |0 | Google CL 1 | 143 |0 | Google DE 1 | 117 |0 | Google IN 1 | 26 |0 | Google NZ 3 | 49 |0 | Google RO 1 | 60 |0 | Google TH 2 | 174 |0 | Yahoo (10 rows) However the percentage is wrong. I can cast one to a float: (count(distinct conversionid) / count(distinct searchid)::float) and it'll give me a better percentage: convcount | searchcount |perc | searchenginename ---+-+-+-- 15 |2884 | 0.00520110957004161 | Google 3 | 49 | 0.0612244897959184 | Google RO 2 | 308 | 0.00649350649350649 | Google CA 2 | 174 | 0.0114942528735632 | Yahoo 1 | 110 | 0.00909090909090909 | Google AU 1 | 25 |0.04 | Google CL 1 | 143 | 0.00699300699300699 | Google DE 1 | 117 | 0.00854700854700855 | Google IN 1 | 26 | 0.0384615384615385 | Google NZ 1 | 60 | 0.0167 | Google TH (10 rows) (I think the answer to this is 'no' but I'm going to ask anyway :P) Is there an easier way to get the more-detailed percentage (it's meant to work in multiple databases - so casting to a float won't work for other db's) ? More importantly... Is there a better way to write the query (I don't like the count(distinct...) but it works and gives the right info) ? I tried to do it with a union: SELECT count(searchid), searchenginename from tp_search s where userid=1 group by searchenginename union select count(conversionid), originfrom from tp_conversions c where c.userid=1 group by originfrom; but then realised that getting the data out with php would be a nightmare (plus I can't get the percentages). Lastly: db=# SELECT version(); version --- PostgreSQL 7.4.3 on i386-pc-linux-gnu, compiled by GCC 2.95.4 (1 row) (I know it's a little out of date, upgrading later this week). Any suggestions/hints/tips welcome :) Thanks, Chris.
Re: [GENERAL] Interpreting query plan
Stephan, Thanks for your reply. Here is the output of explain analyze. I also replaced by simple values with a real query that gets run and (according to our profiling) takes a long time. However, the query is now taking a much shorter period of time than it was profiled at by the application. I can only guess the difference has something to do with system load. I could try to run this again during a high-load period, if that is necessary. Let me know. miqweb= explain analyze select distinct t0.* from UserAccount t0, UserMapping t1 where (t0.companyid = 628) and ((t0.companyid = 628) and (t0.userid = t1.use rid) and (t1.groupid in (628,948,949,950,951,953,954,1272,1279,1296,1299,1300,1 363,1423,1446,1467,1526,1724,1735,1759,1763,1772,1785,1841,1862,1975,2721,2800 , 2801,2802,2803,1264,1394,1525,1662,1843,1844,1845,1396,1528,1860,1846,1762,242 2 ,1271,1847,1848,1281,1849,1850,1851,1266,1809,1852,1853,2421,1854,1855,1913,18 5 6,1857,1269,1268,1858,1859,2804))) and (t0.companyid = 628); [...] Unique (cost=952.15..959.37 rows=289 width=55) (actual time=137.130..143.363 r ows=752 loops=1) - Sort (cost=952.15..952.87 rows=289 width=55) (actual time=137.123..138.0 04 rows=1328 loops=1) Sort Key: t0.userid, t0.companyid, t0.username, t0.password, t0.isact ive, t0.isregistered, t0.lastlogin, t0.firstname, t0.lastname - Hash Join (cost=869.15..940.34 rows=289 width=55) (actual time=112 .112..130.948 rows=1328 loops=1) Hash Cond: (outer.userid = inner.userid) - Seq Scan on useraccount t0 (cost=0.00..55.71 rows=629 width= 55) (actual time=0.239..8.501 rows=753 loops=1) Filter: (companyid = 628) - Hash (cost=866.28..866.28 rows=1151 width=4) (actual time=11 1.762..111.762 rows=0 loops=1) - Seq Scan on usermapping t1 (cost=0.00..866.28 rows=115 1 width=4) (actual time=4.251..109.563 rows=1328 loops=1) Filter: ((groupid = 628) OR (groupid = 948) OR (group id = 949) OR (groupid = 950) OR (groupid = 951) OR (groupid = 953) OR (groupid = 954) OR (groupid = 1272) OR (groupid = 1279) OR (groupid = 1296) OR (groupid = 1299) OR (groupid = 1300) OR (groupid = 1363) OR (groupid = 1423) OR (groupid = 1446) OR (groupid = 1467) OR (groupid = 1526) OR (groupid = 1724) OR (groupid = 1735) OR (groupid = 1759) OR (groupid = 1763) OR (groupid = 1772) OR (groupid = 1785) OR (groupid = 1841) OR (groupid = 1862) OR (groupid = 1975) OR (groupid = 2721) OR (groupid = 2800) OR (groupid = 2801) OR (groupid = 2802) OR (groupid = 2803) OR (groupid = 1264) OR (groupid = 1394) OR (groupid = 1525) OR (groupid = 1662) OR (groupid = 1843) OR (groupid = 1844) OR (groupid = 1845) OR (groupid = 1396) OR (groupid = 1528) OR (groupid = 1860) OR (groupid = 1846) OR (groupid = 1762) OR (groupid = 2422) OR (groupid = 1271) OR (groupid = 1847) OR (groupid = 1848) OR (groupid = 1281) OR (groupid = 1849) OR (groupid = 1850) OR (groupid = 1851) OR (groupid = 1266) OR (groupid = 1809) OR (groupid = 1852) OR (groupid = 1853) OR (groupid = 2421) OR (groupid = 1854) OR (groupid = 1855) OR (groupid = 1913) OR (groupid = 1856) OR (groupid = 1857) OR (groupid = 1269) OR (groupid = 1268) OR (groupid = 1858) OR (groupid = 1859) OR (groupid = 2804)) Total runtime: 144.690 ms (11 rows) -- www.designacourse.com The Easiest Way to Train Anyone... Anywhere. Chris Smith - Lead Software Developer/Technical Trainer MindIQ Corporation ---(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
[GENERAL] Interpreting query plan
(userid) REFERENCES useraccount(userid) $2 FOREIGN KEY (groupid) REFERENCES groups(groupid) -- www.designacourse.com The Easiest Way to Train Anyone... Anywhere. Chris Smith - Lead Software Developer/Technical Trainer MindIQ Corporation ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Wire protocol v3 details
Hi, I'm attempting to port some JDBC driver modifications to 7.4 so I can migrate an application. Where can I find an overview of the wire protocol v3? -- www.designacourse.com The Easiest Way to Train Anyone... Anywhere. Chris Smith - Lead Software Developer/Technical Trainer MindIQ Corporation ---(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] Very long time to commit or close connections
Erwin, Could this be related to a network problem ? Like RST not received by the client, making it believe the server did not acknowledge the connection.close(), eating up the server's resources ? No firewall/VPN gateways between you and your customer ? Nice thought, but the app and database are on the same box, and communicating via TCP/IP on the localhost address. I'm not sure it's even possible to set up a firewall there, and I'm certain it hasn't been done. Thanks, -- www.designacourse.com The Easiest Way to Train Anyone... Anywhere. Chris Smith - Lead Software Developer/Technical Trainer MindIQ Corporation ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Very long time to commit or close connections
Oops, meant to copy the list on this... - Original Message - From: Chris Smith [EMAIL PROTECTED] To: Tom Lane [EMAIL PROTECTED] Sent: Monday, January 19, 2004 8:01 AM Subject: Re: [GENERAL] Very long time to commit or close connections There are not that many things happening during connection close. Does your app use temp tables? LISTEN/NOTIFY? Nope. It's actually a very simple application. It uses plain select, insert, update, and delete statements with nothing particularly unusual beyond that. Nope ... how about looking at the contents of the pg_locks system view when this happens? What external conditions correspond to the instant when the hangs are released? (I do not believe that it just happens by magic --- look for background cron jobs starting or finishing at that time, eg a cron job that issues VACUUMs.) Hmm... I will definitely look. I pretty much know that it's not a vacuum. I suppose I could have our system monitor insert information from the 'ps' utility when there are outstanding connections. What PG version is this, anyway? 7.3.2. We could upgrade within the 7.3 series, but have not qualified the application on 7.4 at this point. (there are complications, mostly because of a patch we apply to the JDBC drivers, which change for 7.4). -- www.designacourse.com The Easiest Way to Train Anyone... Anywhere. Chris Smith - Lead Software Developer/Technical Trainer MindIQ Corporation ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Re: Database Recovery Help...
Hi, Did you try what it suggests? If you're sure PostgreSQL isn't already running, remove the /tmp/.s.PGSQL.5432 file, and try again. I recently came across a problem wherein I am not able to start my postgreSQL 7.0.3 server. Here's what happens..;-( postgres@kahoy /root$ postmaster -D /var/lib/pgsql/data FATAL: StreamServerPort: bind() failed: Permission denied Is another postmaster already running on that port? If not, remove socket node (/tmp/.s.PGSQL.5432) and retry. /usr/bin/postmaster: cannot create UNIX stream port There is no one binding the 5432 port, and no one is trying to connect to that port. I may be left with no other alternative and restart from scratch...;-( There are some pretty important data currently in the database that I'd like to recover... Is there any way wherein I could recover or reconstruct the data? -- Chris Smith http://www.squiz.net ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Re: Report Writer for PostgreSQL
Hey, Does anyone have experience with report writers? It's not something I know much about yet. Crystal Reports (www.seagate.com) is quite good, can do any sort of report you're after. You can use an ODBC driver for accessing the database, so accessing pgsql wouldn't be a problem.. Oops just read the last comment, it is a bit expensive. Maybe not then :) Original Message Subject: Re: [ANNOUNCE] New PostgreSQL Review at epinions.com Date: Thu, 26 Apr 2001 21:19:08 -0400 (EDT) From: Patrick Lanphier [EMAIL PROTECTED] To: Justin Clift [EMAIL PROTECTED] Well it needs the capability format data on many different graph, capable of generating HTML, PDF, and RTF formats. The server will be running on Linux but the design platform can be whatever. The problem I had with one report writer was the data from the database was present one way and it was not capable on rotating the data for the graph and I wasn't about to do this for the report writer. Is there somebody I should contact that you know about a report writer? Patrick Lanphier The Artemis Group http://www.artemisgroup.com On Fri, 27 Apr 2001, Justin Clift wrote: No problem Patrick. :-) Two questions : a) Which operating system(s) does it need to run on? b) What features does it need to have? Honestly, I haven't done much with report writing, so I'm not going to be the best person to ask. BUT if you do a quick subscribe to the [EMAIL PROTECTED] mailing list and ask there, many capable people are around. :-) (You subscribe by sending subscribe as a message to [EMAIL PROTECTED] Unsubscribing later on is the same, but sending unsubscribe without the quotes too) :-) Regards and best wishes, Justin Clift Patrick Lanphier wrote: Sorry to grab your email address and ask you this. But I have been looking for an inexpensive report writer that is feature rich. What are your thoughts? Patrick Lanphier The Artemis Group http://www.artemisgroup.com -- Chris Smith http://www.squiz.net ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
