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
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,
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
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.
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.
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
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.
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.
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
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
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
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,
>
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
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?
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.
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.
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
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
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
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.
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
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
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
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
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
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
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
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
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
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
>
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
?
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
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
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
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
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
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
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
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
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
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
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
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
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
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 (
\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.
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
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
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
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.
50 matches
Mail list logo