Re: [GENERAL] Tables used in query not listed in EXPLAIN

Alvaro Melo writes: > I have a peculiar situation here. I'm optimizing some queries and I > noticed that EXPLAIN doesn't always show the tables involved in the > query. Below is a little example, but I have other examples with longer > queries. Note that the tables tabela_documento_fiscal and p

Re: [GENERAL] tables ending with _copy appeared in the database

On Feb 5, 2014, at 12:50 PM, Eliott wrote: > Dear community, > > I've just noticed that in one of our databases many duplicate tables had > appeared all ending in _copy. These tables are owned by postgres not the db > owner user, other than that they seem to be a replica from a specific time

Re: [GENERAL] Tables with lots of dead tuples despite autovacuum

On Wed, Sep 12, 2012 at 5:00 PM, Wells Oliver wrote: > We have a few tables that are updated nightly on the order of deleting and > inserting 500k records. Out of how many in total? > I assumed autovacuum would do its thing and clean up the dead tuples, but in > looking at pg_stat_user_tables, I

Re: [GENERAL] Tables with lots of dead tuples despite autovacuum

On 09/12/12 5:00 PM, Wells Oliver wrote: We have a few tables that are updated nightly on the order of deleting and inserting 500k records. I assumed autovacuum would do its thing and clean up the dead tuples, but in looking at pg_stat_user_tables, I notice there are lots and lots of dead tup

Re: [GENERAL] Tables creation date and time

Hello 2011/12/7 Prashant Bharucha > Hello All > > Could you please help me to find out of tables creation date and time in > database ? > > Thanks > P Bharucha > If you have a PostgreSQL 9.1, you can use a security hooks and write own custom extension, that will store a timestamp to somewhere.

Re: [GENERAL] Tables creation date and time

On Wed, Dec 7, 2011 at 12:09 PM, Prashant Bharucha wrote: > > Hello All > > Could you please help me to find out of tables creation date and time in > database ? Generally speaking the easiest and simplest way is to log such information and then go through the logs to find out. Otherwise, there

Re: [GENERAL] tables getting bloated

Thanks for the help 2010/3/18 Craig Ringer > On 18/03/2010 9:48 PM, akp geek wrote: > >> I have job that does the vacuum full every day for those 2 tables and >> also for the database. By the end of the day, the get bloated. >> > > > http://wiki.postgresql.org/wiki/Introduction_to_VACUUM%2C_ANAL

Re: [GENERAL] tables getting bloated

On 18/03/2010 9:48 PM, akp geek wrote: I have job that does the vacuum full every day for those 2 tables and also for the database. By the end of the day, the get bloated. http://wiki.postgresql.org/wiki/Introduction_to_VACUUM%2C_ANALYZE%2C_EXPLAIN%2C_and_COUNT http://wiki.postgresql.org/wiki/

Re: [GENERAL] tables getting bloated

2010/3/18 akp geek : > I have job that does the vacuum full every day for those 2 tables and also > for the database. By the end of the day, the get bloated. Do not run vacuum full. Run vacuum. For these two tables, run vacuum more often, as you clearly are updating it very frequently. You may

Re: [GENERAL] tables getting bloated

I have job that does the vacuum full every day for those 2 tables and also for the database. By the end of the day, the get bloated. Regards 2010/3/18 Devrim GÜNDÜZ > On Thu, 2010-03-18 at 09:35 -0400, akp geek wrote: > > I have been doing vacuum full on the database. A > > Why? VF does not h

Re: [GENERAL] tables getting bloated

On Thu, 2010-03-18 at 09:35 -0400, akp geek wrote: > I have been doing vacuum full on the database. A Why? VF does not help you to get rid of bloat -- actually it will create more bloat. -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http

Re: [GENERAL] tables filled to 1 Gig each

On Tue, Apr 7, 2009 at 1:36 PM, Cha Yang wrote: > Postgresql 8.2 > backend to a Java application with JDBC > > > For whatever reason all of postgresql tables all filled up to 1 gig each > Accessing Postgresql through either the application or PGadmin fails When they get to 1gig they start a new s

Re: [GENERAL] tables referenced from insert...returning

"Michael Shulman" <[EMAIL PROTECTED]> writes: > On Mon, Jun 23, 2008 at 8:46 PM, Tom Lane <[EMAIL PROTECTED]> wrote: >> Multiple evaluations of NEW in the text of a rule are a great way >> to cause yourself trouble --- consider what happens if there's >> a volatile function such as nextval() involv

Re: [GENERAL] tables referenced from insert...returning

On Mon, Jun 23, 2008 at 8:46 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > Hmm ... that might be a bug, but in any case, wouldn't it be wiser to do > > CREATE RULE _insert AS ON INSERT TO tv DO INSTEAD > INSERT INTO test (name) VALUES (NEW.name) RETURNING test.*; Well, what I'm really trying to do is

Re: [GENERAL] tables referenced from insert...returning

"Michael Shulman" <[EMAIL PROTECTED]> writes: > CREATE RULE _insert AS ON INSERT TO tv DO INSTEAD > INSERT INTO test (name) VALUES (NEW.name) RETURNING NEW.*; > ERROR: invalid reference to FROM-clause entry for table "*NEW*" > LINE 2: INSERT INTO test (name) VALUES (NEW.name) RETURNING NEW.*;

Re: [GENERAL] Tables dissapearing

On 28/08/2007 15:48, Jeff Amiel wrote: Alien or supernatural intervention: 18-1 Obscure postgresql bug nobody else has ever seen: 25-1 That's the sort of confidence in the DBMS we all like to see! :-) Ray. --- Raymond O'Donnell,

Re: [GENERAL] Tables dissapearing

At 03:15 PM 8/28/2007, Kamil Srot wrote: Andrew, Alvaro... well, sure SQL injection is possibility I cannot ignore... (and sure as "dad" of this application, I think it's not the case :-) ... just kidding... As even the injected SQL will be shown in the logs, so we'll know more after some time.

Re: [GENERAL] Tables dissapearing

On Aug 28, 2007, at 9:41 AM, Kamil Srot wrote: Jeff Amiel wrote: My entire shop has set up a betting pool on the outcome of this...so I hope you post results regardless of the outcome, Kamil. Heh, is the pool still open? Maybe I can make at least something from it :-D (in all seriousness,

Re: [GENERAL] Tables dissapearing

Kamil Srot wrote: Heh, is the pool still open? Maybe I can make at least something from it :-D Current odds Application bug: even money Application configuration issue: 2-1 Rogue cron job or other maintenance process: 4-1 Somebody messing with you (or SQL injection): 8-1 XID wrap

Re: [GENERAL] Tables dissapearing

Jeff Amiel wrote: My entire shop has set up a betting pool on the outcome of this...so I hope you post results regardless of the outcome, Kamil. Heh, is the pool still open? Maybe I can make at least something from it :-D (in all seriousness, we hope you find/fix the problem before things get

Re: [GENERAL] Tables dissapearing

My entire shop has set up a betting pool on the outcome of this...so I hope you post results regardless of the outcome, Kamil. (in all seriousness, we hope you find/fix the problem before things get really ugly) Kamil Srot wrote: Richard Huxton wrote: Kamil Srot wrote: In the version used o

Re: [GENERAL] Tables dissapearing

Richard Huxton wrote: Kamil Srot wrote: In the version used on this particular server, there is no automatic/programing way of changing the schema. Upgrades are done manually and application itself doesn't need schema changes for routine operations... In that case, you can settle the matter

Re: [GENERAL] Tables dissapearing

Kamil Srot wrote: In the version used on this particular server, there is no automatic/programing way of changing the schema. Upgrades are done manually and application itself doesn't need schema changes for routine operations... In that case, you can settle the matter by making sure your app

Re: [GENERAL] Tables dissapearing

On Mon, Aug 27, 2007 at 05:58:26PM -0400, Andrew Sullivan wrote: > On Mon, Aug 27, 2007 at 11:44:38PM +0200, Kamil Srot wrote: > > When this problem appeared for the first time, I had clearly the > > wraparound problem... I did vacuum it and partially restored the data... > > I don't think vacuu

Re: [GENERAL] Tables dissapearing

Alvaro Herrera wrote: Adrian Klaver wrote: I have a question. First a little history. Right now, the people who know better than I are fairly certain Postgres is not changing things on its own and the developer is certain the CMS software is not doing schema changes. As I understand it log

Re: [GENERAL] Tables dissapearing

Tom Lane wrote: Erik Jones <[EMAIL PROTECTED]> writes: Also, I'd write a simple "ping" script to check for the table that runs every 5 seconds or so. I had gathered that the table was being touched constantly by his app, so that it'd be immediately obvious when it had gone away. If th

Re: [GENERAL] Tables dissapearing

Adrian Klaver wrote: I have a question. First a little history. Right now, the people who know better than I are fairly certain Postgres is not changing things on its own and the developer is certain the CMS software is not doing schema changes. As I understand it logging has been cranked up to

Re: [GENERAL] Tables dissapearing

On Tue, Aug 28, 2007 at 02:03:29AM +0200, Kamil Srot wrote: > The system is actually management of website&eshop with webbased UI ... Um, are you sure you don't have a SQL-injection problem, and someone is doing Something Bad to you? A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that tec

Re: [GENERAL] Tables dissapearing

Erik Jones <[EMAIL PROTECTED]> writes: > Also, I'd write a simple "ping" script to check for the table that > runs every 5 seconds or so. I had gathered that the table was being touched constantly by his app, so that it'd be immediately obvious when it had gone away. If that's not so, then defini

Re: [GENERAL] Tables dissapearing

On Aug 27, 2007, at 7:57 PM, Kamil Srot wrote: Tom Lane wrote: Kamil Srot <[EMAIL PROTECTED]> writes: Erik Jones wrote: Have you verified that the table's files are still on disk after it's "disappeared"? Do not have any idea how to do it... I wasn't able to access it using any DML/DDL co

Re: [GENERAL] Tables dissapearing

Adrian Klaver wrote: > I have a question. First a little history. Right now, the people who know > better than I are fairly certain Postgres is not changing things on its own > and the developer is certain the CMS software is not doing schema changes. As > I understand it logging has been crank

Re: [GENERAL] Tables dissapearing

On Monday 27 August 2007 5:57 pm, Kamil Srot wrote: > Tom Lane wrote: > > Kamil Srot <[EMAIL PROTECTED]> writes: > >> Erik Jones wrote: > >>> Have you verified that the table's files are still on disk after > >>> it's "disappeared"? > >> > >> Do not have any idea how to do it... I wasn't able to ac

Re: [GENERAL] Tables dissapearing

Tom Lane wrote: Kamil Srot <[EMAIL PROTECTED]> writes: Erik Jones wrote: Have you verified that the table's files are still on disk after it's "disappeared"? Do not have any idea how to do it... I wasn't able to access it using any DML/DDL commands... can try it on a binary

Re: [GENERAL] Tables dissapearing

Kamil Srot <[EMAIL PROTECTED]> writes: > Erik Jones wrote: >> Have you verified that the table's files are still on disk after >> it's "disappeared"? > Do not have any idea how to do it... I wasn't able to access it using > any DML/DDL commands... can try it on a binary backup of the damaged DB

Re: [GENERAL] Tables dissapearing

Tom Lane wrote: Kamil Srot <[EMAIL PROTECTED]> writes: From above mentioned duplications of relatio names and what Tom wrote recently (doesn't see like WA problem), it looks like the relation name is/gets corrupted in some way and this corruption is internally taken over to another instanc

Re: [GENERAL] Tables dissapearing

brian wrote: Just wondering: what's the name of this table? What's its function? I think you mentioned that it's pretty well static. But what is it holding? Maybe that'll provide a clue as to where to look. The system is actually management of website&eshop with webbased UI ... the table is

Re: [GENERAL] Tables dissapearing

Erik Jones wrote: On Aug 27, 2007, at 4:44 PM, Kamil Srot wrote: Also, in your original post you mentioned a "proprietal CMS system". Is this proprietary to your company or one that you've purchased? The fact that the same table going on multiple dbs all being run by that CMS system certain

Re: [GENERAL] Tables dissapearing

Kamil Srot wrote: brian wrote: IMHO, it's not at all improbable, given that this software is connecting to the same databases you are seeing affected by this phenomenon. Not to mention that it's proprietary, so fewer eyes have gone over it.[1] I suggested earlier grepping for 'drop' in your

Re: [GENERAL] Tables dissapearing

Andrew Sullivan wrote: On Mon, Aug 27, 2007 at 11:44:38PM +0200, Kamil Srot wrote: When this problem appeared for the first time, I had clearly the wraparound problem... I did vacuum it and partially restored the data... I don't think vacuum would "fix" a wrap around problem in the wa

Re: [GENERAL] Tables dissapearing

brian wrote: Kamil Srot wrote: > Erik Jones wrote Also, in your original post you mentioned a "proprietal CMS system". Is this proprietary to your company or one that you've purchased? The fact that the same table going on multiple dbs all being run by that CMS system certainly makes it wo

Re: [GENERAL] Tables dissapearing

On 8/27/07, Kamil Srot <[EMAIL PROTECTED]> wrote: > I really need to make sure, the next problem (if it's necessary to let > it happen) will be the last one... :-( Have you set your log_statement to 'ddl' in postgresql.conf? That way you'll have a log of every single ddl that goes through the da

Re: [GENERAL] Tables dissapearing

On Aug 27, 2007, at 4:44 PM, Kamil Srot wrote: Also, in your original post you mentioned a "proprietal CMS system". Is this proprietary to your company or one that you've purchased? The fact that the same table going on multiple dbs all being run by that CMS system certainly makes it wort

Re: [GENERAL] Tables dissapearing

Kamil Srot <[EMAIL PROTECTED]> writes: > From above mentioned duplications of relatio names and what Tom wrote > recently (doesn't see like WA problem), it looks like the relation name > is/gets corrupted in some way and this corruption is internally taken > over to another instance of relation

Re: [GENERAL] Tables dissapearing

On Mon, Aug 27, 2007 at 11:44:38PM +0200, Kamil Srot wrote: > When this problem appeared for the first time, I had clearly the > wraparound problem... I did vacuum it and partially restored the data... I don't think vacuum would "fix" a wrap around problem in the way you describe. I don't think

Re: [GENERAL] Tables dissapearing

Kamil Srot wrote: > Erik Jones wrote Also, in your original post you mentioned a "proprietal CMS system". Is this proprietary to your company or one that you've purchased? The fact that the same table going on multiple dbs all being run by that CMS system certainly makes it worthy of suspic

Re: [GENERAL] Tables dissapearing

Also, in your original post you mentioned a "proprietal CMS system". Is this proprietary to your company or one that you've purchased? The fact that the same table going on multiple dbs all being run by that CMS system certainly makes it worthy of suspicion. This is software developed in

Re: [GENERAL] Tables dissapearing

On Aug 27, 2007, at 4:08 PM, Tom Lane wrote: Kamil Srot <[EMAIL PROTECTED]> writes: # select xmin, age(xmin) from pg_class; xmin|age ---+ 2 | 2147483647 2 | 2147483647 2 | 2147483647 2 | 2147483647 2 | 2147483647

Re: [GENERAL] Tables dissapearing

Tom Lane wrote: Kamil Srot <[EMAIL PROTECTED]> writes: # select xmin, age(xmin) from pg_class; xmin|age ---+ 2 | 2147483647 2 | 2147483647 2 | 2147483647 2 | 2147483647 2 | 2147483647 2 | 2147483647 23683801

Re: [GENERAL] Tables dissapearing

Kamil Srot <[EMAIL PROTECTED]> writes: > # select xmin, age(xmin) from pg_class; >xmin|age > ---+ > 2 | 2147483647 > 2 | 2147483647 > 2 | 2147483647 > 2 | 2147483647 > 2 | 2147483647 > 2 | 2147483647 > 236838019

Re: [GENERAL] Tables dissapearing

Andrew Sullivan wrote: I can easily rewrite it to use the vacuumdb command, but I doubt it'll make any difference. The point is that you don't have to rewrite it. Just run "vacuumdb -a" and it vacuums _all_ databases. Oh, I have it now! It takes some time, but at the end, I'll unders

Re: [GENERAL] Tables dissapearing

On Mon, Aug 27, 2007 at 10:31:11PM +0200, Kamil Srot wrote: > The script is very simple one: Well, I don't see anything obvious, but. . . > I can easily rewrite it to use the vacuumdb command, but I doubt it'll > make any difference. The point is that you don't have to rewrite it. Just run "va

Re: [GENERAL] Tables dissapearing

Andrew Sullivan wrote: On Mon, Aug 27, 2007 at 10:03:04PM +0200, Kamil Srot wrote: Sure, I ment it like I'll do the FULL vacuum less often than daily and do daily the plain vacuum command. If you have your servers set up correctly, you should never need to perform VACUUM FULL.

Re: [GENERAL] Tables dissapearing

On Mon, Aug 27, 2007 at 10:03:04PM +0200, Kamil Srot wrote: > > > Sure, I ment it like I'll do the FULL vacuum less often than daily and > do daily the plain vacuum command. If you have your servers set up correctly, you should never need to perform VACUUM FULL. > Well, I do list all database

Re: [GENERAL] Tables dissapearing

Andrew Sullivan wrote: On Mon, Aug 27, 2007 at 07:15:44PM +0200, Kamil Srot wrote: OK, I'll drop the full part and do it less often... This doesn't address your problem, but when you move from VACUUM FULL to VACUUM, you want to do it _more_ often, not less. Sure, I ment it like I'l

Re: [GENERAL] Tables dissapearing

On Mon, Aug 27, 2007 at 07:15:44PM +0200, Kamil Srot wrote: > OK, I'll drop the full part and do it less often... This doesn't address your problem, but when you move from VACUUM FULL to VACUUM, you want to do it _more_ often, not less. But given what you've posted, I am not even a little bit con

Re: [GENERAL] Tables dissapearing

Alvaro Herrera wrote: Kamil Srot wrote: Martijn van Oosterhout wrote: On Mon, Aug 27, 2007 at 06:57:54PM +0200, Kamil Srot wrote: Correct...the script does echo "vacuum full;" | $PGDIR/bin/psql -U postgres $db for each database... Hope it's correct? Well, I'd

Re: [GENERAL] Tables dissapearing

Kamil Srot wrote: > > Martijn van Oosterhout wrote: >> On Mon, Aug 27, 2007 at 06:57:54PM +0200, Kamil Srot wrote: >> >>> Correct...the script does echo "vacuum full;" | $PGDIR/bin/psql -U >>> postgres $db for each database... >>> Hope it's correct? >>> >> >> Well, I'd drop the "full" part

Re: [GENERAL] Tables dissapearing

Martijn van Oosterhout wrote: On Mon, Aug 27, 2007 at 06:57:54PM +0200, Kamil Srot wrote: Correct...the script does echo "vacuum full;" | $PGDIR/bin/psql -U postgres $db for each database... Hope it's correct? Well, I'd drop the "full" part, it tends to bloat indexes. Also, did you c

Re: [GENERAL] Tables dissapearing

On Mon, Aug 27, 2007 at 06:57:54PM +0200, Kamil Srot wrote: > Correct...the script does echo "vacuum full;" | $PGDIR/bin/psql -U > postgres $db for each database... > Hope it's correct? Well, I'd drop the "full" part, it tends to bloat indexes. Also, did you check it was actually completing (no e

Re: [GENERAL] Tables dissapearing

Martijn van Oosterhout wrote: On Mon, Aug 27, 2007 at 06:37:17PM +0200, Kamil Srot wrote: I don't say, it's gone by itself, I'm asking for help debuging this situation and hopefully find a solution. For the first time it happened, it had the same symptoms - this specific table was missing an

Re: [GENERAL] Tables dissapearing

On Mon, Aug 27, 2007 at 06:37:17PM +0200, Kamil Srot wrote: > I don't say, it's gone by itself, I'm asking for help debuging this > situation and hopefully find a solution. For the first time it happened, > it had the same symptoms - this specific table was missing and > transaction counter was

Re: [GENERAL] Tables dissapearing

brian wrote: Tom Lane wrote: Kamil Srot <[EMAIL PROTECTED]> writes: One more thing: The project runs proprietal CMS system and there are more instances of it with the same database layout in different databases. Every time the "lost" table is the same one - the bussiest one (mostly read)..

Re: [GENERAL] Tables dissapearing

Tom Lane wrote: Kamil Srot <[EMAIL PROTECTED]> writes: One more thing: The project runs proprietal CMS system and there are more instances of it with the same database layout in different databases. Every time the "lost" table is the same one - the bussiest one (mostly read)... and everytime

Re: [GENERAL] Tables dissapearing

Kamil Srot <[EMAIL PROTECTED]> writes: > One more thing: > The project runs proprietal CMS system and there are more instances of > it with the same database layout in different databases. Every time the > "lost" table is the same one - the bussiest one (mostly read)... and > everytime the "lost

Re: [GENERAL] tables not in list

> -Original Message- > From: Viatcheslav Kalinin [mailto:[EMAIL PROTECTED] > Sent: Tuesday, July 31, 2007 9:16 AM > To: Lee Keel > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] tables not in list > > I usually resolve this as: > -- this function le

Re: [GENERAL] tables not in list

Lee Keel wrote: Is there no way to do this without doing an insert into another table? I usually resolve this as: -- this function lets you select from an array CREATE OR REPLACE FUNCTION "array_to_set" (vaarray anyarray) RETURNS SETOF anyelement AS $body$ BEGIN FOR I IN COALESCE(ARRAY_LOW

Re: [GENERAL] tables not in list

Is there no way to do this without doing an insert into another table? _ From: paddy carroll [mailto:[EMAIL PROTECTED] Sent: Monday, July 30, 2007 3:24 PM To: Lee Keel Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] tables not in list put all your tables in a new table

Re: [GENERAL] tables not in list

put all your tables in a new table (public.tables) select table_name from public.tables where public.tables.table_name not in (select table_name from information_schema.tables where table_catalog='postgres' and table_type='BASE TABLE' and table_schema='public') On 30 Jul 2007, at 20:31,

Re: [GENERAL] Tables not created in proper schema

Ashish Karalkar wrote: > ${PG_PATH}/psql -d qsweb --command "CREATE SCHEMA qsweb" > > ${PG_PATH}/psql -d qsweb --command "ALTER SCHEMA qsweb OWNER TO qsweb" > > ${PG_PATH}/psql --command "ALTER USER qsweb SET SEARCH_PATH='qsweb'" > > > The output is right till alter schema but then while setti

Re: [GENERAL] tables are not listable by \dt

On Jul 2, 2007, at 10:04 AM, Rajarshi Guha wrote: Hi, we recently upgraded our Postgres instllation from 7.4 to 8.2 by doing a dump and restore. Howveer after logging into the database (as a user that is not the superuser) and doing \dt I get the error: No relations found But when I do

Re: [GENERAL] tables are not listable by \dt

On 7/2/07, Rajarshi Guha <[EMAIL PROTECTED]> wrote: Hi, we recently upgraded our Postgres instllation from 7.4 to 8.2 by doing a dump and restore. Howveer after logging into the database (as a user that is not the superuser) and doing \dt I get the error: No relations found Are you using th

Re: [GENERAL] tables are not listable by \dt

Rajarshi Guha <[EMAIL PROTECTED]> writes: > Hi, we recently upgraded our Postgres instllation from 7.4 to 8.2 by > doing a dump and restore. Howveer after logging into the database (as > a user that is not the superuser) and doing \dt I get the error: > No relations found > But when I do > SELE

Re: [GENERAL] tables are not listable by \dt

On Mon, Jul 02, 2007 at 10:04:21AM -0400, Rajarshi Guha wrote: > Hi, we recently upgraded our Postgres instllation from 7.4 to 8.2 by > doing a dump and restore. Howveer after logging into the database (as > a user that is not the superuser) and doing \dt I get the error: > > No relations foun

Re: [GENERAL] Tables not created in proper schema

schmer" <[EMAIL PROTECTED]> To: Sent: Monday, July 02, 2007 2:28 PM Subject: Re: [GENERAL] Tables not created in proper schema am Mon, dem 02.07.2007, um 11:13:54 +0530 mailte Ashish Karalkar folgendes: Hello All, I am trying to create databse with script. I run this script from root

Re: [GENERAL] Tables not created in proper schema

am Mon, dem 02.07.2007, um 11:13:54 +0530 mailte Ashish Karalkar folgendes: > Hello All, > I am trying to create databse with script. > I run this script from root prompt with command > > > $ su - postgres -c 'path to script.sql' > > > In the script I follow following steps > > > 1) cr

Re: [GENERAL] tables with lots of columns - what alternative from performance point of view?

On 12/7/05, Oleg Bartunov wrote: contrib/hstore will save you.See http://www.sai.msu.su/~megera/postgres/gist/hstore/README.hstorefor details. thanks. i didn't know about it, and it looks great. but i'm not sure if we will be able to use it - my developers use java + hibernate, a

Re: [GENERAL] tables with lots of columns - what alternative from

contrib/hstore will save you. See http://www.sai.msu.su/~megera/postgres/gist/hstore/README.hstore for details. Oleg On Wed, 7 Dec 2005, hubert depesz lubaczewski wrote: hi jus recently there were some thread on postgresql list with people asying : i have 700 columns, i have 1000 column

Re: [GENERAL] Tables

On 10/20/05 12:37 PM, "Bob Pawley" <[EMAIL PROTECTED]> wrote: > I am new to databases. > > I have table 1, a primary source, which generates a serial number to make each > item unique. > > I want to use this number to generate a row in table 2 linking the two rows > and allowing specific informa

Re: [GENERAL] Tables

On Thu, Oct 20, 2005 at 09:37:07AM -0700, Bob Pawley wrote: > I have table 1, a primary source, which generates a serial number > to make each item unique. Do you mean that the table has a serial column (which is just a convenient way to declare an integer column that takes its default value from

Re: [GENERAL] tables in public

OK.thanks...I suppose I don't need it. - Original Message - From: "Magnus Hagander" <[EMAIL PROTECTED]> To: "Zlatko Matić" <[EMAIL PROTECTED]>; Sent: Saturday, August 06, 2005 9:55 PM Subject: RE: [GENERAL] tables in public Could you,

Re: [GENERAL] tables in public

> Could you, please, give me short description of > functionalities of those modules? tsearch2 is full text indexing. postgis is geographical functionality. //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map set

Re: [GENERAL] tables in public

Zlatko Matić" <[EMAIL PROTECTED]>; Sent: Saturday, August 06, 2005 9:28 PM Subject: RE: [GENERAL] tables in public Hello. Postgres automatically included some tables in my public domain: pg_ts_cfg, pg_ts_cfgmap,pg_ts_dict, pg_ts_parser, I beleive these are from tsearch2. spatial_ref_sy

Re: [GENERAL] tables in public

> Hello. > > Postgres automatically included some tables in my public domain: > pg_ts_cfg, pg_ts_cfgmap,pg_ts_dict, pg_ts_parser, I beleive these are from tsearch2. > spatial_ref_sys. And this is postgis. > I suppose it's because I switched on all additional options > during initdb... Yes

Re: [GENERAL] tables & retrieving their names

On Mon, Feb 21, 2005 at 03:45:42PM -0800, Mohsen Pahlevanzadeh wrote: > 2.How i see name of my tables in shell? > 3.How i see name of my DBs in shell? See the psql documentation: http://www.postgresql.org/docs/8.0/static/app-psql.html See also "System Catalogs" and "The Information Schema": ht

Re: [GENERAL] Tables and Indexes

Hi, Even in Beta, it is just fine to me! When I create a index, the tablespace used is the table's tablespace, unless I use the tablespace clause to put it in another one. It is ok, I have read documentation and learned that. I was looking for a default configuration, so ALL the indexes created

Re: [GENERAL] Tables and Indexes

Patrick Fiche wrote: Hi, I think that TABLESPACE is what you need... It's now available in Postgresql, just look at the syntax in documentation. Actually it is only available in Beta. You will have to wait a little while longer for stable release. Sincerely, Joshua D. Drake

Re: [GENERAL] Tables and Indexes

Hi, I think that TABLESPACE is what you need... It's now available in Postgresql, just look at the syntax in documentation. > -- - > Patrick Fiche > email : [EMAIL PROTECTED] > tél : 01 69 29 36 18 >

Re: [GENERAL] Tables as function arguments

On Wednesday 24 March 2004 04:31, Jeremy Semeiks wrote: > Hello, > > I use psql interactively for data exploration, and often I want to do > the same types of generic set operations on data in many different > tables. For example, to calculate a similarity metric between 2 > different tables I migh

Re: [GENERAL] Tables Referencing themselves As Foreign Keys

This is a fine approach. The FK will work fine. You'll probably want CatID to be NOT NULL and CatParent to allow nulls. Having a Null parent indicating root is easier for traversals. Common other features to add include: a "path" column that is maintaned by insert/update triggers. Quite e

Re: [GENERAL] tables, where

On Thu, 29 Mar 2001, Richard Huxton wrote: > From: "Martín Marqués" <[EMAIL PROTECTED]> > > > Is it a bad idea to put tables of different aplications, even if they have > > somethings in comun, in the same database? > > Plus points : > Minus points : As I have not received an answer to my pos

Re: [GENERAL] tables, where

From: "Martín Marqués" <[EMAIL PROTECTED]> > Hi > Is it a bad idea to put tables of different aplications, even if they have > somethings in comun, in the same database? > > Saludos... :-) Plus points : + If you need to join between tables you will *need* to do this Minus points : - You will

Re: [GENERAL] Tables Question

Hi, > How can I retrieve the "names" of all the tables in my database ? Not sure what you are using but if you use DBI perl interface you can use this two functions : $sth = $dbh->table_info; @names = $dbh->tables; They works well with DBD::Pg. In psql simply type \d. this is certainly n

Re: [GENERAL] Tables Question

Try typing "\dt" from the psql prompt of the database whose table list u want On Thu, 17 Aug 2000 [EMAIL PROTECTED] wrote: > How can I retrieve the "names" of all the tables in my database ? > > TIA >