[ADMIN] who can tell me the correct syntax to use dblink to talk to a table in another database

I installed the dblink module, run dblink.sql to my current database to use the dblink function, but I have not been successful to select a table from a remote database (cross reference). Whoever knows how to use dblink, please show me the syntax to select another table in a remote database. Th

[ADMIN] query becomes much slower after Postgres 8.2.4 and Postgis 1.2.1 upgrade

We had the following upgrades last week: proj-4.4.9 to proj-4.5.0 geos-2.2.1 to geos-2.2.3 Postgis 1.1.1 to Postgis 1.2.1, then Postgres 8.1.4 to 8.2.4 After the entire upgrades, a same query with geospatial functions that used to come back in a few minutes, now take about 3 minutes to finish,

[ADMIN] select from dblink very slow

I need to joint select from table A in the current database A and dblink to table B in database B, the result coming from tableB in db B is very slow because I have to select all the rows from tableB from dblink then do "where" with table A in the current dbdoing dblink this way is very very

[ADMIN] select count(*) from a large table is very slow

I have a ~30 milliow row table, select count(*) from this table is very very very slow... How dow I get it run faster? Thanks, Jessica - We won't tell. Get more on shows you hate to love (and love to hate): Yahoo! TV's Guilty Pleasures list.

[ADMIN] cross database references...

Other than dblink, is there any other way to let me joint select two tables in two different databases? Thanks, Jessica - Park yourself in front of a world of choices in alternative vehicles. Visit the Yahoo! Auto Green Center.

Re: [ADMIN] cross database references...

scheams work fine in ONE db, I need to have two databass and they need to able to talk to eacher, dblink funciton is working fine for me now, I am just wondering if there is any other better way than dblink... Thanks, Jessica Scott Marlowe <[EMAIL PROTECTED]> wrote: Jessica Richard

[ADMIN] select * from a 3M-row table very slow

Is there anything that can make this faster? Is there any memory config (postgres and OS) that can be tuned? it came back very quick under Informix. Thanks. - Looking for earth-friendly autos? Browse Top Cars by "Green Rating" at Yahoo! Autos' Green Center.

[ADMIN] how to get a list of members of a postgres group?

Creatred a group, added members to the group, what command to use to check the current members in the group? thanks. - We won't tell. Get more on shows you hate to love (and love to hate): Yahoo! TV's Guilty Pleasures list.

[ADMIN] Can primary key be dropped and added back in?

I have a huge table to load (30+M rows). Dropping indexes before loading will speed up my process, but I am not sure about the primary key that was created with the table creation. Is the Postgres primary key treated like an index? If yes, dropping the primary key might help my loading as well

[ADMIN] "_" in a serach pattern

I have a huge table that has a char(80) name column (indexed). The name pattern is like A_B_C-D.123.456.pdf A_B_C-D.123.333.doc.2 When I select name from table where name like 'A_B%', it was doing a table scan; I guess the underscore "_" was treated like a special character, instead of a char

[ADMIN] select a range of data

If I have a column with a value like "abc2457", "efg1234", how do I select col from table where col like 'a[a-z]c24[0-9]7"? How do I handle the range bracket in the query? Thanks. - Boardwalk for $500? In 2007? Ha! Play Monopoly Here and Now (it's update

Re: [ADMIN] "_" in a serach pattern

Peter, thanks a lot for your reply. Could you please tell me more What is the difference between varchar and text? what is the benefit of each one? and is text alway better than varchar ?--- when it comes to a string column... thanks Peter Koczan <[EMAIL PROTECTED]> wrote: Hi, Jessica, >

[ADMIN] varchar vs text columns

What is the difference between varchar and text for a string column? What is the benefit of using each one? Is text better than varchar performance wise? Thanks. - Park yourself in front of a world of choices in alternative vehicles. Visit the Yahoo! Auto G

[ADMIN] primary key and foreign key

Just want to be very clear that: 1. adding a primary key will have an implicit index automatically on the column(s). 2. Adding a foreign key will NOT have an index created automatically, it only does the referencing work. If an index is needed on the F-key column, it has to be created, right?

[ADMIN] Does Postgres 8.x support stored procedure?

I remember the lower version of Postgres did not support system stored procedure, only functions. Not sure about the latest version... Thanks. - Got a little couch potato? Check out fun summer activities for kids.

[ADMIN] cpu usage on the linux machine

If the linux box has 8 CPU's, is there a way to configure for Postgres to user 6 of them? how? Thanks! - Fussy? Opinionated? Impossible to please? Perfect. Join Yahoo!'s user panel and lay it on us.

[ADMIN] server closed the connection unexpectedly

A psql connection was started from one Linux machine, talking to the Postgres server on another Linux machine (both the postgres client and the Postgres server are 8.2.4). Got the following error: If this a Postgres disconnection or a disconnection on the OS side? Thanks!!! server closed th

[ADMIN] how to kill a Postgres connection on the list of select * from pg_stat_activity?

Sometime a script talking to the Postgres has already been killed on the OS level, but the Postgres for this script is still listed out of select * from pg_stat_activity, saying a particular database is still being accessed by this script. How to kill (release) this connection in this case? Th

[ADMIN] could not create IPv6 socket when starting Postgres server

A new Postgres server 8.2.4 was installed on an IRIX platform, but I was unable to bring up the Postgres server clean. When using postmaster to start the server, the server is up but with the IPv6 error. when using "pg_ctl start" to start the server, the Postgres server won't come up at all

[ADMIN] Postgres can be started only when one postgres user is logged in

I have never see this before In order to start my postgres server, I have to log out all my Postgres windows...and I I have two postgres user windonw open, the server just won't come up... as soon as I close all postgres windows leaving only one widow open, then I can start Postgres server.

[ADMIN] how to change the port number for the Postgres server

Everything was running fine under the default number 5432. After the port number was changed to another number in postresql.conf file and the server was restarted, dblink is broken, it is still looking for the old port number 5432... not sure if everything else is broken as well... So, how to

[ADMIN] hanging Postgres connections....

I am seeing a lot of hanging Postgres connections on one Postgre server (IRIX platform). The main user uses a perl script talking to the database (insert, update, etc). Those hanging connections are in "IDLE" state and they look very old... Why are those jobs not releasing their connections whe

Re: [ADMIN] hanging Postgres connections....

Postgres: 8.2.4 IRIX: IRIX64 machinename 6.5 Thanks. Jessica "Jonah H. Harris" <[EMAIL PROTECTED]> wrote: On 9/24/07, Jessica Richard wrote: > I am seeing a lot of hanging Postgres connections on one Postgre server > (IRIX platform). The main user uses a perl

[ADMIN] how to create an user with ONLY select permission and nothing else?

I created a test user with the following command: $ createuser test Shall the new role be a superuser? (y/n) n Shall the new role be allowed to create databases? (y/n) n Shall the new role be allowed to create more new roles? (y/n) n CREATE ROLE This user at the moment does not have permission to

[ADMIN] Is there a way to kill a connection from the pg_stat_activitly list?

When you see a hanging Postgres connection (or a job running so long and you don't want to continue any more) from select * from pg_stat_activity and you want to disconnect it, how do you do it? thanks, Jessica - Check out the hottest 2008 models tod

Re: [ADMIN] Is there a way to kill a connection from the pg_stat_activitly list?

y. thanks Tommy Gildseth <[EMAIL PROTECTED]> wrote: Jessica Richard wrote: > When you see a hanging Postgres connection (or a job running so long > and you don't want to continue any more) from > > select * from pg_stat_activity > > and you want to disconnect it, &g

Re: [ADMIN] Is there a way to kill a connection from the pg_stat_activitly list?

If the connection is from the local machine, I can find it with "ps -ef | grep procpid", then kill it with Unix command "kill" outside Postgres... But I have many remote connections coming from different machines...it is hard to kill on the OS level outside Postgres on the postgres host... I am

Re: [ADMIN] Is there a way to kill a connection from the pg_stat_activitly list?

nd a safer, cleaner way to disconnect a user from Postgres when needed. Thanks a lot, Scott Marlowe <[EMAIL PROTECTED]> wrote: On 10/15/07, Jessica Richard wrote: > Thanks a lot! > > "select pg_cancel_backend(procpid) " can end the current query for that > user, but th

[ADMIN] how to check objects dependency??

I am trying to drop a user but it complained with the following message: ERROR: role "joe" cannot be dropped because some objects depend on it DETAIL: 1 objects in database Jan_test 1 objects in database Jan this user does not own any tables in those two databases. Is there any command I can u

Re: [ADMIN] how to check objects dependency??

t;[EMAIL PROTECTED]> wrote: Jessica Richard writes: > I am trying to drop a user but it complained with the following message: > ERROR: role "joe" cannot be dropped because some objects depend on it > DETAIL: 1 objects in database Jan_test > 1 objects in database Jan >

[ADMIN] select only user

I created a user and granted only "select" to this user so that this user can only select the tables on a production system. But by default, this user can also CREATE TABLE successfully How can I revoke this "create table" privilege (and possible others) and limit this user to only the per

Re: [ADMIN] select only user

? Thanks again, Jessica "Milen A. Radev" <[EMAIL PROTECTED]> wrote: Milen A. Radev написа: > Jessica Richard написа: >> I created a user and granted only "select" to this user so that this >> user can only select the tables on a production sys

[ADMIN] invalid memory alloc request

An insert query had been running fine until lately with the following memory error: Can't execute statement: ERROR: invalid memory alloc request size 18446744072762367400 It is running on Postgres 8.2.4, and the database size is less than 2G. T he requested memory size is ridiculous high, does

[ADMIN] index performance

I have a large table with about 2 million rows and it will keep growing... I need to do update/inserts, and select as well. An index will speed up the select, but it will slow down the updates. Are all Postgres indexes ordered? i.e., with every update, the index pages will have to be physically

[ADMIN] slow delete...

I have a table with 29K rows total and I need to delete about 80K out of it. I have a b-tree index on column cola (varchar(255) ) for my where clause to use. my "select count(*) from test where cola = 'abc' runs very fast, but my actual "delete from test where cola = 'abc';" takes forever, ne

Re: [ADMIN] slow delete...

x27;::text) (4 rows) Thanks again, Jessica - Original Message From: Charles Duffy <[EMAIL PROTECTED]> To: Jessica Richard <[EMAIL PROTECTED]> Cc: pgsql-admin@postgresql.org Sent: Friday, July 4, 2008 3:17:43 AM Subject: Re: [ADMIN] slow delete... > by the way, there is a foreign key

[ADMIN] how to get fy info from the parent table

Could some one tell me how to check this pk/fy thing please? >From the parent table, I can see only the primary key there, now I wan to know >how many and what other foreign keys are referencing this primary key... did >this check long time ago and totally forgot how I did it then Thanks a

[ADMIN] How many table scans in a delete...

I am just trying to understand how Postgres does its delete work. If I have a table testDad with a primary key on cola and no index on colb, and I have a kid table testKid with a foreign key to reference testDad but no index created on the foreign key column on table testKid. I have 10,000 rows

[ADMIN] performance cost for varchar(20), varchar(255), and text

I am tuning a database created by someone else. I noticed that some column lengths were defined longer than needed. For example, an Id column is holding a stand length of 20 characters but was defined as varchar(255). On some other columns, for example, a Description column is supposed to hold

[ADMIN] best starting points....

On a new system with 64G memory, what is the best starting points for shmmax on OS and shared_buffers for Postgres. All databases combined are about 50G. I want to evaluate this with the two following scenarios: 1. this machine is running only Postgres, no other applications 2. this machine has

[ADMIN] time stamp and durations in postgres log file

in my Postgres log file, I see something like this: usename dbname, .., logtimestamp1... ... insert then the next line: username, dbname, ... logtimestamp2... duration: 0.2 ms My questions: 1. Is this 0.2 ms how much time the insert takes to finish? 2. does "logtimestamp2 - logtimestamp

[ADMIN] Postgres 8.3.x upgrade

We are going to upgrade our Postgres servers (most 8.2.9 and some 8.2.4) to the 8.3.x version. >From the postgres web site, I can see the 8.3.5 is the latest version. Question for people who have been on 8.3.5: Is 8.3.5 very safe to use (no major new bugs)? Is it really better than the older

[ADMIN] vacuum full...

I am running "vacuum full" via a shell script for a list of large databases now... and I may run out of my scheduled system down time If I don't finish all databases and kill the script in the middle... am I going to cause any table corruptions since "vacuum full" is rebuilding the tables an

[ADMIN] how to figure out how long a query takes in the pg log file...

I need to identify the slowest queries by analyzing the postgre log file. By the following log_line_prefix set up, I can see all the query contents and I can see the duration times: log_line_prefix = '%u %d %h %m %c %x ' .. .. postgres testdb [local] 2009-03-10 14:36:35.110 EDT 49b6b31a.8

[ADMIN] Postgres log file

I am curious about this scenario: What is going to happen when a server is running and the Postgres log file is accidentally deleted or renamed? The server will have no place to write its log entries obviously.. what else?. Is there any way to check within Postgres what the current log name (

[ADMIN] how to check the creation date of a postgres database?

\l and  \l+ show only the database name, owner and description.select * from pg_database does not have date info, either.I would like to know when each of my databases were created.Thanks,Jessica Get your own web address for just $1.99/1st yr. We'll help. Yahoo! Small Business.

Re: [ADMIN] how to check the creation date of a postgres database?

It didn't seem to work I just checked one $PGDATA/base directory,  they all have today's time stamp (9/20/06)  around 6:00 am this morning, that was when my daily database dump cron job was running... thanks anyway Chris Mair <[EMAIL PROTECTED]> wrote: > \l and \l+ show only the database na

Re: [ADMIN] how to check the creation date of a postgres database?

Thanks, but I still can't get the database creation date% cat PG_VERSION8.116% ls -lt PG*-rw---    1 postgres users  4 Jun 22 10:34 PG_VERSIONThis date was the date that the Postgres server was upgraded to 8.1 and the timestamp of this file is same under each of all database directo

[ADMIN] how do I create a db with unicode encoding on a utf8 server

I have a Postgre server with UTF8 as the default encoding. I am trying to create a new database on this same server with UNICODE encoding so that I can load an UNICODE database from another server. No matter how I specify unicode option on this utf8 server, it just created a utf8 db for me any

[ADMIN] how do I load a 8.0.8 database with unicode encoding into 8.1.4/utf8?

got an errro when loading a db from 8.0.8 with unicode to 8.1.4 server with UTF8 server: ERROR: invalid byte sequence for encoding "UTF8": 0x92 thanks, Jessica - Everyone is raving about the all-new Yahoo! Mail beta.