[GENERAL] Record order change after update
Hello list, After update a column on a table, that row goes to the top when I do a select from that table without any order, is that the expected behavior in postgresql? is there a way to prevent it? Thanks in advance. Josué Maldonado ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] pg_dump and schema namespace notes
Bruce Momjian <[EMAIL PROTECTED]> writes: > Any comments on this? It seems like a valid confusion. What solutions > are there? I think we're stuck. We can't avoid the fact that the SQL syntax uses the keyword SCHEMA to mean a namespace. We also can't avoid the very common usage of "database schema" to mean the logical structure of your table collection. Trying to redefine or avoid either of these terms is hopeless. Maybe we could add some text in the tutorial to point out the double meaning? regards, tom lane ---(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] Postgresql vs. aggregates
[EMAIL PROTECTED] writes: > I'd find all this much easier to reason about if I understood how > the versions of a row are organized and accessed. How does postgresql > locate the correct version of a row? It doesn't, particularly. A seqscan will of course visit all the versions of a row, and an indexscan will visit all the versions matching the indexscan key-column conditions. It's up to the "time qualification" tests (tqual.c) to accept only the version that is visible to your transaction. There are two or three implementation choices in the btree index routines that are intended to increase the chances that you'll hit the version you want sooner rather than later. But they're only heuristics. The bottom line is that we check all the versions till we find the right one. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] tablespaces and schemas
Andrew Rawnsley <[EMAIL PROTECTED]> writes: > Schemas are users in Oracle, but the net effect to the SQL author is > the same. 'SELECT * FROM SERVICES.USERS' is the same, just that > 'SERVICES' is a user in oracle (although referred to as a schema, and > you have to do a 'CREATE SCHEMA AUTHORIZATION blablabla' to get > anything to work. Oracle is actually SQL-spec-compliant in this regard (or possibly I should say the spec is Oracle-compliant, seeing that they probably dictated these semantics...) The SQL spec is carefully written so that an implementation that enforces one-to-one matching of schemas and users is spec-compliant. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] How to drop sequence?
On Mon, 1 Mar 2004, Ron St-Pierre wrote: > You're right I am getting the same results. I created the same table with: >create table category ( > category_id serial not null primary key, > category_name character varying(100) not null >); > > alter table category alter column category_id drop default; > ALTER TABLE > >drop sequence public.category_category_id_seq; >ERROR: cannot drop sequence category_category_id_seq because table > category column category_id requires it >HINT: You may drop table category column category_id instead. > > and it won't let me drop the sequence, even if I drop the default for > the column first. Does anyone know if this is the way this is supposed > to work, and if so, how to remove the dependency on it from the column > category_id? OK, here's a story... Once upon a time, whenever you created a table with a SERIAL column, it was implemented with a sequence that had no dependency tracking. This meant that when you dropped the table, the sequence was still there. Given the simplistic method used to assign the name of a sequence created by the SERIAL macro, this meant that future attempts to create said table again would fail. Witness, the wonder of postgresql 7.2: postgres=# create table test (id serial, info text); NOTICE: CREATE TABLE will create implicit sequence 'test_id_seq' for SERIAL column 'test.id' NOTICE: CREATE TABLE / UNIQUE will create implicit index 'test_id_key' for table 'test' CREATE postgres=# drop table test; DROP postgres=# create table test (id serial, info text); NOTICE: CREATE TABLE will create implicit sequence 'test_id_seq' for SERIAL column 'test.id' NOTICE: CREATE TABLE / UNIQUE will create implicit index 'test_id_key' for table 'test' ERROR: Relation 'test_id_seq' already exists But, with 7.3 the dependency tracking system started keeping track of the sequences created by the SERIAL macro, thus making it possible to have these things disappear when uneeded. This is with 7.4: postgres=# create table test (id serial, info text); NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for "serial" column "test.id" CREATE TABLE postgres=# drop table test; DROP TABLE postgres=# create table test (id serial, info text); NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for "serial" column "test.id" CREATE TABLE postgres=# Note there's now no error with an undropped sequence. But, as with all progress, it came with a price. In the past, many users had used the serial macro and gotten used to the behaviour it exhibited, including myself, I must admit. The agreement was made that from then on, if you wanted sequences to be tracked by dependency, use serial, if you want to have them be standalone you'd have to create them yourself. Maybe there's a more complex way of handling dependencies that might fix this minor issue, like automatically tracking everytime a sequence is assigned to a clause in another table, but that might get caught in circular references and go kaboom if I wrote it. :-) Now, if you want to uncouple them, you're playing in the database catalogs, which is as close to an unsupported feature as anything in postgresql can be. Do a \dS in a psql session to see all the public catalog. I'm not even sure where to start myself, and I wouldn't recommend doing anything to the catalogs on a production server. ---(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] Data in table changed?
On 2004.03.02 08:04 Thomas Holmgren wrote: I need an efficient way of determining if data in a table has been changed (either updated, deleted or inserted). Can this be achieved without scanning the tables using expensive SQL? (my tables are _big_!) Is there some way to get a "time for last update" for a specific table? Or something similar I can use? You could always make table of 'last changed' timestamps with columns of tablename and timestamp. Then a function which takes the name of the table and updates the last_changed table with a timestamp is called from a trigger for insert, update, and delete of each table. The CREATE TRIGGER would pass the name of the table to the function. Karl <[EMAIL PROTECTED]> Free Software: "You don't pay back, you pay forward." -- Robert A. Heinlein ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Setting up Postgresql on Linux
On 01/03/2004 23:22 Phil Campaigne wrote: Hello, I originally installed postgresql as root user and now I am setting up a development environment with cvs and a java ide and tomcat. I have everything with the exception of postgresql integreted using a non-root user. THe process I am using is to logon as postges and start the database and do queries from the command line using psql. Then I logoff and logon as phil and start tomcat and my java ide. 1.Is there a better way to start the database and the web application? Start PostgreSQL off as service (it's probably there already but configured to start on power-up/reboot). In a production environment I set tomcat up as a service as well. In a developmemt environment, I install/run it under my user. 2. During setup of postgresql I chown and ghgrp the /usr/local/pgsql directoy tree to postgres? 3.However my development environment(i.e. jdbc connection from my application) doesn't seem to have permission to access to all the files it needs in postgres? It doesn't need any postgres files. It communicates via tcp/ip. Keith has already posted the config changes you need to make. 4. Is is best to install postgresql as user "phil" in order to gain access for my integrated develoment environment? See above. -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] pg_dump and schema namespace notes
Bruce Momjian wrote: Any comments on this? It seems like a valid confusion. What solutions are there? A common interchangeable title for schema is namespace and in actuality we use \dn in psql (\dnamespace) to list them. Perhaps we should change the name? I believe Oracle uses the term namespace as well. Sincerely, Joshua D. Drake --- Rory Campbell-Lange wrote: I think this is a suggestion/comment! pg_dump man page: --schema-only Dump only the schema (data definitions), no data I think this use of the word schema is confusing, meaning data definitions, whereas elsewhere in the man page schema are used as a namespace definition. --schema=schema Dump the contents of schema only. If this option is not speci- fied, all non-system schemas in the target database will be dumped It would be nice if this could be a comman separated list (like set search_path; A,info,public etc). Rory -- Rory Campbell-Lange <[EMAIL PROTECTED]>---(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 -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL
Re: [GENERAL] sql query to get field types
On Mon, 1 Mar 2004, Alexander Cohen wrote: > What sql query can i use to get all relative data to the types of > fields that a table contains? select column_name,data_type from information_schema.columns where table_name='test'; works in 7.4 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Solaris ecpg program doesn't work - pulling my hair
Jan, is this fixed in current CVS and 7.4.X CVS? --- Jan Wieck wrote: > [EMAIL PROTECTED] wrote: > > >> We had this in the past. I'm not sure and would have to search the > >> archives but I vaguely remember that this has been a threading bug in > >> the Solaris version. Could you please try using 7.4.2 or cvs head where > >> this should be fixed. Alternatively you could try with threadding > >> disabled. > > > > I verified last night that this problem also occurs with 7.4.2. I did some > > more extensive testing on the solution in my previous follow-up email. That > > is definitely the problem - configure is setting "-pthread" instead of > > "-lpthread" in config.status. After manually correcting this in > > config.status, everything works properly. > > As stated before, this is not true. If you don't compile with > -D_REENTRANT, the /usr/include/errno.h declared errno as > > extern int errno; > > instead of the thread safe > > extern int *___errno(); > #define errno *(___errno()) > > At least it does so here on Solaris 8. That leads to libpq using the > global errno variable, which might or might not be the one where "your" > error is in a multithreaded program. I mailed the correct solution as a > follow up to the other thread earlier today as a patch against 7.4.2. > > > > > I don't know enough about configure to know how to fix configure. It is > > properly setting -lpthread on linux. > > Just linking against the right libraries does not do it here. Solaris is > not Linux. > > > Jan > > > > > > > It's also not clear why the symptoms occur since the build does not abort > > with an unsatisfied external. It must be picking up the pthread externals > > from soemwhere else? The only difference I can se in the ldd's is the order > > of the libraries. An ldd of ecpglib shows: > > > > Good: > > > > gcc -shared -h libecpg.so.4 execute.o typename.o descriptor.o data.o error.o > > prepare.o memory.o connect.o misc.o -L../../../../src/port > > -L/mhinteg/trees/4/sun32_fixes/ported/openssl -L../pgtypeslib -lpgtypes > > -L../../../../src/interfaces/libpq -lpq -lssl -lcrypto -lm -lpthread > > -R/home/wrp/local/pgsql.7.4.2/lib -o libecpg.so.4.1 > > rm -f libecpg.so.4 > > ln -s libecpg.so.4.1 libecpg.so.4 > > rm -f libecpg.so > > ln -s libecpg.so.4.1 libecpg.so > > > > % ldd libecpg.so > > libpgtypes.so.1 => > > /home/wrp/local/pgsql.7.4.2/lib/libpgtypes.so.1 > > libpq.so.3 =>/home/wrp/local/pgsql.7.4.2/lib/libpq.so.3 > > libssl.so.0.9.7 => > > /mhinteg/trees/4/sun32_fixes/ported/openssl/libssl.so.0.9.7 > > libcrypto.so.0.9.7 => > > /mhinteg/trees/4/sun32_fixes/ported/openssl/libcrypto.so.0.9.7 > > libm.so.1 => /usr/lib/libm.so.1 > > libpthread.so.1 => /usr/lib/libpthread.so.1 > > libresolv.so.2 =>/usr/lib/libresolv.so.2 > > libsocket.so.1 =>/usr/lib/libsocket.so.1 > > libnsl.so.1 => /usr/lib/libnsl.so.1 > > libdl.so.1 =>/usr/lib/libdl.so.1 > > libc.so.1 => /usr/lib/libc.so.1 > > libmp.so.2 =>/usr/lib/libmp.so.2 > > libthread.so.1 =>/usr/lib/libthread.so.1 > > /usr/platform/SUNW,Ultra-Enterprise/lib/libc_psr.so.1 > > > > Bad: > > > > gcc -shared -h libecpg.so.4 execute.o typename.o descriptor.o data.o error.o > > prepare.o memory.o connect.o misc.o -L../../../../src/port > > -L/mhinteg/trees/4/sun32_fixes/ported/openssl -L../pgtypeslib -lpgtypes > > -L../../../../src/interfaces/libpq -lpq -lssl -lcrypto -lm -pthread > > -R/home/wrp/local/pgsql.7.4.2/lib -o libecpg.so.4.1 > > gcc: unrecognized option `-pthread' > > rm -f libecpg.so.4 > > ln -s libecpg.so.4.1 libecpg.so.4 > > rm -f libecpg.so > > ln -s libecpg.so.4.1 libecpg.so > > > > % !ldd > > ldd libecpg.so > > libpgtypes.so.1 => > > /home/wrp/local/pgsql.7.4.2/lib/libpgtypes.so.1 > > libpq.so.3 =>/home/wrp/local/pgsql.7.4.2/lib/libpq.so.3 > > libssl.so.0.9.7 => > > /mhinteg/trees/4/sun32_fixes/ported/openssl/libssl.so.0.9.7 > > libcrypto.so.0.9.7 => > > /mhinteg/trees/4/sun32_fixes/ported/openssl/libcrypto.so.0.9.7 > > libm.so.1 => /usr/lib/libm.so.1 > > libresolv.so.2 =>/usr/lib/libresolv.so.2 > > libsocket.so.1 =>/usr/lib/libsocket.so.1 > > libnsl.so.1 => /usr/lib/libnsl.so.1 > > libpthread.so.1 => /usr/lib/libpthread.so.1 > > libdl.so.1 =>/usr/lib/libdl.so.1 > > libc.so.1 => /usr/lib/libc.so.1 > > libmp.so.2 =>/usr/lib/libmp.so.2 > > libthread.so.1 =>/usr/lib/libthread.so.1 > > /usr/platform/SUNW,Ultra-Enterprise/lib/libc_psr.so.1 > > > > > > > > I realize it isn't entirely meaningful without the source code to know > > exactly where I put the print statements, but here is my debug output > > running the previously
Re: [GENERAL] pg_dump and schema namespace notes
Any comments on this? It seems like a valid confusion. What solutions are there? --- Rory Campbell-Lange wrote: > I think this is a suggestion/comment! > > pg_dump man page: > > --schema-only > Dump only the schema (data definitions), no data > > I think this use of the word schema is confusing, meaning data > definitions, whereas elsewhere in the man page schema are used as a > namespace definition. > >--schema=schema > Dump the contents of schema only. If this option is not > speci- fied, all non-system schemas in the target > database will be dumped > > It would be nice if this could be a comman separated list (like set > search_path; A,info,public etc). > > Rory > -- > Rory Campbell-Lange > <[EMAIL PROTECTED]> > > > ---(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 > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] SERIAL type - auto-increment grouped by other field
:(( sorry At server ther is istalled pgsql 7.1.2 but creatlang works from 7.2.X and upper version :( do you have any other ideas?? elein wrote: You just need to define 'plpgsql' as a language in your database. At the shell, to define the language use: createlang 'plpgsql' ; After you do the createlang, you can write functions using plpgsql. Let me know if you are still having trouble. --elein On Mon, Mar 01, 2004 at 09:52:33PM +0100, Paulovi?? Michal wrote: tnx a lot, but i am using PostgreSQL 7.1.2 and your script result errors: --- ERROR: Unrecognized language specified in a CREATE FUNCTION: 'plpgsql'. Recognized languages are sql, C, internal, and created procedural languages. --- elein wrote: One possible implementation of a two level numbering outline will be published in PostgreSQL GeneralBits Issue #64 due out Monday morning, 3/1. http://www.varlena.com/GeneralBits/ elein [EMAIL PROTECTED]Varlena, LLCwww.varlena.com 1-866-VARLENA PostgreSQL Consulting, Support & Training PostgreSQL General Bits http://www.varlena.com/GeneralBits/ = Its a doggy dog world out there. On Sun, Feb 29, 2004 at 02:44:01AM +0100, Paulovi? Michal wrote: hi all, I have problem with SERIAL field type (or sequence functionality). I have table with three columns - ID, IDS, NAME. I want auto-increment IDS grouped by ID. Example: 1, 1, Ferdo 1, 2, John 2, 1, Martin 1, 3, Elvira 2, 2, Georgia but... when I use IDS - SERIAL and when I create UNIQUE (ID, IDS) result is: 1, 1, Ferdo 1, 2, John 2, 3, Martin 1, 4, Elvira 2, 5, Georgia where i make misstake??? how can i do it??? in documentation there is description only for one auto-increment column. I didn't find auto increment as I described upper. Do you have any idea how can I do it??? tnx a lot Information from NOD32 This message was checked by NOD32 Antivirus System for Linux Mail Server. http://www.nod32.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) Information from NOD32 This message was checked by NOD32 Antivirus System for Linux Mail Server. http://www.nod32.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] Information from NOD32 This message was checked by NOD32 Antivirus System for Linux Mail Server. http://www.nod32.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] md5 calls
On Sat, 21 Feb 2004, Simon Windsor wrote:
> Hi
>
>
>
> I am using the standard debian testing release of postgres(7.3.4) and was
> wondering how to produce and md5 string.
>
>
>
> I had thought
>
>
>
> Select md5('joe');
>
>
>
> Would be sufficient?
Doesn't that work? It works for me in 7.4. I don't run 7.3 on any
servers (they're all either older 7.2 boxes or 7.4).
Can you upgrade your postgresql installation to 7.4.1 (or wait a day or
two for 7.4.2 to come out.)
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[GENERAL] pg_dumpall and large objects
hi, the man page says pg_dump cannot dump "large objects". can some one help me on what does this realy mean? how large is large? if we have some icons can we still use pg_dumpall to backup the db? also large objects do not include large strings, right? thanks cheng = Best wishes Z C Wang Yahoo! Messenger - Communicate instantly..."Ping" your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html ---(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
Re: [GENERAL] A simple question (under pressure :-))....
Hi John, Short answer: you can do cross-schema queries, you can't (easily) do cross database queries. Check out this: http://www.postgresql.org/docs/7.4/static/ddl.html , the schema section: http://www.postgresql.org/docs/7.4/static/ddl-schemas.html I'm not sure when schemas were introduced, but I suppose you want to use the latest postgres version. Cheers, Csaba. On Mon, 2004-03-01 at 02:37, John Wells wrote: > Guys, > > I have approx. 8 hours to finish a deadline, so I can no longer spend time searching > google...haven't found the answer yet. > > In PG, is there not a way to select amongst databases? > > In other words, if I have one schema called sch1 and another called sch2, and I'm > connected to sch2, could I not do: > > select fieldname from sch1.tablename > > I'm running 7.2.3, so I think there was no such things as a schema in this > release...just a database, but can't recall. > > It seems I've been able to do this in the past, but I may be remembering wrong. > > Thanks! > John > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] count(1) return 0?
On Mon, 01 Mar 2004 13:12:12 +1100 Klint Gore <[EMAIL PROTECTED]> wrote: > The counting and grouping is done after the where clause is applied. > > since player iplaybadly (who was 1200's opponent) didnt win any, he/she > is not included in the result set to be grouped and counted. You need > to get iplaybadly into the result set first. > > try something like > > select fixture, home, sum(case winner=home then 1 else 0 end) > >from results > group by fixture, home Ah, thanks, works a charm. Thanks too to all who offered help on this one. Ciao Zak -- http://www.carfolio.com/Searchable database of 10 000+ car specs ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] A simple question (under pressure :-))....
Guys, I have approx. 8 hours to finish a deadline, so I can no longer spend time searching google...haven't found the answer yet. In PG, is there not a way to select amongst databases? In other words, if I have one schema called sch1 and another called sch2, and I'm connected to sch2, could I not do: select fieldname from sch1.tablename I'm running 7.2.3, so I think there was no such things as a schema in this release...just a database, but can't recall. It seems I've been able to do this in the past, but I may be remembering wrong. Thanks! John ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] count(1) return 0?
On Mon, 1 Mar 2004 02:14:56 +0200, Zak McGregor <[EMAIL PROTECTED]> wrote: > to return a 0 value instead of absolutely nothing if no rows match fixture=4916 > and winner=away? I get absolutely no results at all. > > any ideas please? dont group by winner. it's not returned in the statement so it's not needed anyway. an exact value is specified in the where clause so it's not going to be different either. klint. +---+-+ : Klint Gore: "Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+ ---(end of broadcast)--- TIP 8: explain analyze is your friend
