PostgreSQL is a powerful, enterprise class, open source RDBMS. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability and data integrity. It runs on all major operating systems, including Linux, UNIX and Windows. It prides ...
2
votes
2answers
92 views
Different queries result in big difference in performance
I have 2 similar functions, different way result in big diff in performance.
The PostgreSQL version: 9.2.1.
Function 1
create or replace function func_1() returns text as
$$
declare
v_i ...
1
vote
1answer
52 views
Equivalent of MRG_MYISAM in databases other than MySQL? [closed]
Does anyone know if other database have something equivalent to MRG_MYISAM (aka the MERGE table type/storage engine)?
I know about fragmenting, but this is not quite the same AFAIK. We're using ...
2
votes
1answer
65 views
PostgreSQL: Cannot change directory to /root
I am trying to copy a table planet_osm_polygon from one database osm to another test. I su postgres and performed the pg_dump.
Problem: However I'm getting the error could not change directory to ...
3
votes
0answers
47 views
How to count selectivity rows in PostgreSQL 8.2
I have problem with two queries on PostgreSQL v8.2. It seem like the optimizer chooses a wrong index:
db=# explain
select count(*), messagetype, alias
from event
where
templateinfoid = 10 and ...
4
votes
1answer
64 views
Recovering a dropped table in PostgreSQL
I have :
CREATE TABLE ketoan_vn.Customers
(
CustomerId character varying(10) NOT NULL,
CustomerName character varying(150),
CONSTRAINT Customers_PK_CustomerID PRIMARY KEY (CustomerId)
);
I ...
0
votes
1answer
45 views
PostgreSQL, How to keep only one schema?
I don't want some users be available to create schemas, and only use the public schema.
I'd like to remove the permission of creating schemas, but I can't found anything on PotsgreSQL 9.1 ...
0
votes
1answer
25 views
look for missing datetime records in database
I have a python script that scrapes a webpage every minute of every day and saves the results in a postgres database.
As I browse through the database, I've noticed there are minutes here and there ...
0
votes
1answer
66 views
PostgreSQL stored procedure to return rows or empty set
I have stored procedure, which RETURNS SETOF ct_custom_type and inside I do
RETURN QUERY EXECUTE 'some dynamic query'
And I want to do this: If this 'dynamic query' returns >= 10 rows, I want to ...
0
votes
1answer
98 views
How to insert binary data into a PostgreSQL BYTEA column using libpqxx?
I'm a newbie at SQL, and even greener when it comes to using the PostgreSQL C++ API libpqxx.
What I'd like to do is insert some binary data into a BYTEA column, but I find the Doxygen output is ...
0
votes
1answer
71 views
Using postgres with the command line without pgAdmin
I started working with PostgreSQL and till now I only had issues with it ( pgAdmin3 issues the most ) now I work a lot with MySQL and sometimes on the command line by just doing
mysql -u ... -h ...
0
votes
1answer
39 views
postgres deadlock without explicit locking
I use PostgreSQL 9.2, and I do not use explicit locking anywhere, neither LOCK statement nor SELECT ... FOR UPDATE. However, recently I got ERROR: 40P01: deadlock detected. The query where deadlock ...
0
votes
2answers
63 views
In Postgresql, is it possible to change the maximum number of columns a table can have?
I'm tasked with keeping a postgres table in sync with a non-postgres table. The non-postgres table has more than 1600 columns. When I try to create the table in postgres, I get:
ERROR: tables can ...
1
vote
1answer
46 views
Pass a table array as a parameter to an upsert function in postgresql
i have an UPSERT function which works well but i update and insert records in batches, can this function be modified so that i just pass all the records to this function as an array then it will ...
0
votes
2answers
57 views
PostgreSQL stored procedure which can return one of two custom rowtypes
I want to have one function, which can return two different types of rows according to given parameter. For example (simplified example) I have function like this:
CREATE OR REPLACE FUNCTION ...
1
vote
1answer
34 views
Drop database from remote that has connected users
I have a "small" problem. There is a database server that contains a database where some test frontends are connected to. I want to update that database from a dump + some scripts to the most recent ...
0
votes
1answer
43 views
How is PostgreSQL extension feature compared to SQL Server extended and CLR stored procedures?
I am new to PostgreSQL. If I am not wrong, SQL Server has similar mechanism - extended stored procedures for adding new functionality to the base SQL Server functionality. CLR stored procedures could ...
-1
votes
2answers
61 views
strategies to get data from diff database [closed]
Problem
I have two databases user_works database and pricing database.
I want to take a join of two tables user_table which is in user_works database & rent_table which is in pricing database ...
2
votes
1answer
59 views
PostgreSQL concurrent increment of counter
I need to maintain a statistical table for a project, composed by a list of items and their usage (Think about something like a website in which you would like to count pageviews). Each time an item ...
1
vote
1answer
48 views
How to allow a user to create databases only with a certain prefix?
We're having a multi-user Linux server. I'd like to give each user the ability to create and manage databases at will, but to keep things sane, force them that each database name they create must ...
0
votes
1answer
41 views
PostgreSQL cannot start after replacing contents inside its Data folder
When recovering the Postgresql database from a hacked Ubuntu 12.04 server, I copied the data directory to another location, reinstalled the OS and PostgreSQL, stopped PostgreSQL service, removed the ...
0
votes
0answers
15 views
Identical Postgres index scan taking 5x longer on server
I have an intermediate table for managing a many-to-many relation between tables called Expert and Subject:
Column | Type | Modifiers
...
1
vote
2answers
44 views
How do I list all schemas in PostgreSQL?
When using PostgreSQL v9.1, how do I list all of the schemas using SQL?
I was expecting something along the lines of:
SELECT something FROM pg_blah;
1
vote
1answer
26 views
Hiding variable input in psql
I am attempting to prompt for a username and password when running a database creation script, and I want to hide any text input for my password prompt. In Oracle's sqlplus, I can use HIDE at the end ...
2
votes
1answer
56 views
How to fine-tune Postgres for IO usage?
I have a Postgres database I am hosting in a quad core server with 8 GB of RAM.
At any time, there can be 100 or so reads and writes occurring concurrently. I am experiencing high IO wait time. When ...
1
vote
4answers
73 views
How to change the data directory on Postgresql 9.1 on Ubuntu
I am having a problem changing the data directory on postgresql 9.1 on ubuntu 13.04:
I first did a:
sudo pg_dropcluster --stop 9.1 main
and then attempted to create a new one in the desired path:
...
1
vote
1answer
50 views
Why does pgAdmin3 set OIDS=FALSE and owner to postgres by default?
Why does pgAdmin3 automatically set the table owner to postgres when its created and set OIDS=FALSE? I am new to PostgreSQL and want to know the rational behind this default behavior.
-- Table: ...
2
votes
2answers
90 views
How to implement posts with “seen by” like facebook?
Inside a Facebook group there is a feature when every time see the group newsfeed it mark all posts as seen by me.
And if I see a specific post inside a group thought a url ...
3
votes
0answers
65 views
Streaming Replication Failover - how to point second slave at new master?
I have a Postgres database HA cluster I've set up in a lab. I'm using PgPool-II to manage fail-over.
For the cluster itself, I have three identical Postgres 9.2 servers set up like so:
Server A - ...
0
votes
2answers
88 views
Use CASE to select columns in UPDATE query?
I can use CASE to choose which columns to display in a SELECT query (Postgres), like so:
SELECT CASE WHEN val = 0 THEN column_x
WHEN val = 1 THEN column_y
ELSE 0
END AS ...
0
votes
1answer
39 views
Postgres sql dump import error
I'm facing a issue while importing the dump in my system, i'm getting the error as
" Error: syntax error at or near "" LOCK TABLESDB_NAME` WRITE;"
and exiting with "out of memory"
We had ...
3
votes
1answer
62 views
In Postgres, How can I merge two tables?
I have a set of web services that all need updating with a new set of static data.
I can't use a distributed transaction, but would like to be able to ask each service to rollback it's changes if I ...
4
votes
1answer
98 views
How to determine if there are [idle connections with] uncommitted transactions in PostgreSQL?
According to a comment on this question I asked about idle connections in PostgreSQL 9.2, some uncommitted transactions (possibly related to some of those idle connections) might cause
some ...
3
votes
1answer
82 views
Could too many idle connections affect PostgreSQL 9.2 performance?
Some queries on my database server seem to take a long time to respond, and I believe the CPU usage is high. When running ps aux, I see ~250 "idle" connections (which I believe to be too many). I ...
1
vote
3answers
59 views
PostgreSQL newbie - how to create objects in a database
I've just finished installing PostgreSQL and pgadmin3 on Ubuntu. Using pgadmin, I've been able to create a new user called 'test' and also a new database with 'test' as the owner.
Now I'm trying to ...
3
votes
3answers
94 views
Is a REINDEX required after CLUSTER?
I'm considering using CLUSTER to reorder a table by an index. I understand that this recreation of the table data makes all the existing indexes either bloat or be useless. I've seen some indications ...
0
votes
0answers
32 views
Define default operator class for gist on enum data type
I have my own enum data type (called direction), which can be IN or OUT and I want to use EXCLUDE USING gist constraint to prevent inserting row with the same direction (and two more columns) like ...
3
votes
3answers
138 views
Optimizing queries on a range of timestamps (two columns)
I use postgresql-9.1 with ubuntu 12.04.
I need to select records inside a range of time: my table time_limits has two timestamp fields and one property integer. Indeed there are other info columns ...
1
vote
0answers
38 views
PGPool Frequent Queries
We recently setup a PostgreSQL Cluster with PGPool in front of it. This is our first go at such a thing. We setup our QA environment to use the new PGPool, and we cranked up the logging on the ...
3
votes
2answers
48 views
disallow SET command in a postgresql server
Im planning to publish my postgresql server to a few untrusted clients.
I dont want them to modify any runtime setting, like work_mem or something risky to my server. In general I assume the ...
1
vote
1answer
40 views
POSTGRES DB Server Blunder
This weekend I had to rebuild my laptop which had Postgres running. For some reason, I forgot to dump the data but I did make a complete copy of the original hard drive.
Is it possible to recover ...
0
votes
3answers
81 views
Incremental backup in postgresql
I want to migrate a production database from one linux server to other.
I am not concern about upgrading my database version, just I want to move the database from one server to other with minimum ...
4
votes
1answer
93 views
Transactions, references and how to enforce double entry bookkeeping? (PG)
Double entry bookkeeping is
a set of rules for recording financial information in a financial
accounting system in which every transaction or event changes at least
two different nominal ...
2
votes
1answer
37 views
How to turn off all RAISE NOTICE on production server?
I put a lot of RAISE NOTICE in functions for debugging purpose. I think this will affect performance, so they should be removed on production environment. Is there any way to opt-out those ...
6
votes
2answers
22 views
Postgres performance difference on insert
I was tried to partition one large table, and come with such Postgres behavior that I could not explain. Maybe you will have any suggestions?
I went quite straight forward way (i know that it is not ...
0
votes
1answer
107 views
Object-oriented programming CRUD design pattern
At first I have posted this question at http://cstheory.stackexchange.com/questions/17170/object-oriented-programming-crud-design-pattern - that's because I think it's a subect question, and it can ...
1
vote
1answer
46 views
How to grant only functions while revoking tables?
I am a newbie to PostgreSQL, and configuring a new database.
I wrote several functions for indirect access(read/write) to data, and I want to prohibit all direct access to table.
GRANT CONNECT ON ...
0
votes
1answer
37 views
Postgres 9.1 wait events
Is there anything in Postgresql 9.1 similar to wait events dynamic views of oracle?
I need to find queries which are waiting for a long time, and the events they are blocked on.
1
vote
0answers
37 views
Write performance of Postgresql 9.1 with read-only slave
I have a Postgresql 9.1 database that is being hosted by Heroku. It currently has a read-only "follower". I need to truncate several large tables (over 100GB) of data and reloads them. Is the ...
0
votes
1answer
35 views
PostgreSQL backup error
I am trying to backup my company PostgreSQL database using pgAdmin III, so I selected our company DB from the tree, then right clicked on it and selected 'Backup', selected the destination folder ...
0
votes
0answers
51 views
How I can copy from local file to remote DB in PostgreSQL?
I am a novice in psql and need some help.
How can I load a local CSV to a remote DB?
I am using the following command
\COPY test(user_id, product_id, value)
FROM '/Users/testuser/test.tsv' ...
