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 ...
4
votes
2answers
141 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 ...
4
votes
1answer
89 views
Materialized View PostgreSQL
Is there any possibility of having built-in materialized views in PostgreSQL ?
I don't want Gardner version or somehthing like physical table populated by triggers.
I've read that was/is something ...
4
votes
2answers
128 views
Select longest continuous sequence
I am trying to construct a query in PostgreSQL 9.0 that gets the longest sequence of continuous rows for a specifc field.
Consider the following table:
lap_id (Serial), lap_no (int), car_type ...
4
votes
1answer
122 views
How should we handle rows which won't be queried once they are old in PostgreSQL?
We have a table in a PostgreSQL database which is growing on the order of millions of rows per day.
Each row consists of:
ID
Foreign user ID
Date and time
Other data
The date and time aren't ...
4
votes
2answers
517 views
PostgreSQL: reuse of complex intermediate result in same query
Using PostgreSQL (8.4), I'm creating a view that summarizes various results from a few tables (e.g creating columns a, b, c in the view), and then I need to combine some of these results together in ...
4
votes
1answer
185 views
Can PostgreSQL tell me what configuration files it's using?
I am having trouble with some changes I'm making to a configuration file. I think PostgreSQL is not using the ones I'm modifying. Is there any way to get postgres (maybe some environment variable) to ...
4
votes
1answer
76 views
Can I activate PITR after the DB has been used
My Postgres-Server is running for quite a while and contains probably 4-5 GB of data.
Backups are done using dumps, and this works ok. Though not most efficient, it just works.
Due to future growth ...
4
votes
1answer
74 views
How can I request a flush of the postgresql transaction logs?
I have the following problem: a "vertical" Linux distribution (Sophos UMT) comes with PostgreSQL 9.2 to store its configuration. Unfortunately, since the last update, it seems that the transaction ...
4
votes
1answer
62 views
How to get the information of the optimizer of PostgreSQL?
I am curious about the information like
total optimization time
the time spent on each stage of optimization
the number of alternative plans and
the cost of each alternative plan.
4
votes
1answer
49 views
Is there better way to 'touch' a row than performing a dummy update?
I use the xmin system column to implement a form of optimistic locking, and sometimes need to 'touch' the row to bump xmin without actually updating the row. I currently just do a 'dummy' update:
...
4
votes
1answer
75 views
Safe way to stop/abort REINDEX
I am running a REINDEX but it takes long time and still working. The application using the DB is not responding right now. Is there a safe way to stop it and do it another time?
PostgreSQL version is ...
4
votes
1answer
61 views
Postgresql, write archive file over mount, secure against simultaneous access?
I am using Postgres 9.2.1 and am saving my archivable WAL over a NFS share.
I just use the basic command, given as an example in the postgresql.conf
test ! -f /mnt/server/archivedir/%f && cp ...
4
votes
1answer
2k views
How to use aes-encryption in PostgreSQL?
I tried aes-encryption by using following statement:
SELECT encrypt('test', 'key', 'aes');
which worked, but I am not able to decrypt the value. I inserted it in a field of datatype bytea but I'm ...
4
votes
1answer
148 views
PostrgeSQL 9.1 Certificate Authentication: how to get certificate common name of client?
I am using Certificate Authentication for clients on a PostgreSQL 9.1 database server. Several certificate common names are mapped to a single database user in pg_ident.conf.
I do not want to create ...
4
votes
1answer
239 views
Upgrade Postgres without doing a pg_dumpall
I'm upgrading from postgres 8.4 to 9.0 and looking at the manual it looks like this is my only choice:
pg_dumpall -p 5432 | psql -d postgres -p 6543
Which means I'll need to stop any writes to the ...
4
votes
1answer
1k views
How to get actually allocated tablespace size by each postgres database?
Postgres tablespace is cluster-wide. Therefore, how do I caculated how much space allocated by each database in postgres tablespace ?
4
votes
1answer
54 views
Want to add a “variable” to a table
I'm using PostgreSQL 8.4 and I have a table that is cleared out and refilled with new data every so often. I want to be able to store the date that the table was last filled, but I'm not sure where to ...
4
votes
2answers
140 views
Deadlock during Slony uninstall and concurrent access of DB
I am using slony1-2.0.4.rc2 and postgresql-8.4.0-0. I faced a deadlock in the slony uninstall process.
I was trying to uninstall node from a cluster, as per slony's documents this requires access ...
4
votes
2answers
472 views
Postgresql 9.0 adding new database to streaming replication
postgresql 9.0 streaming replication + hot standby is running. Now i need to add one more database to current cluster. What is the best way of doing this? I have a backup file about 20GB. should i ...
4
votes
1answer
361 views
Is the PostgreSQL 'connection limit' shared across all users in a role?
I have created a role that many users can share:
> create role foo_read_only connection limit 5;
I have then assigned permissions to that role:
> grant select on table1 to foo_read_only;
...
4
votes
3answers
82 views
PostgreSQL group roles
This is not a "group by" question. I am struggling with permissions in postgres because I apparently don't understand how group roles work. Searching for this on SO has proven difficult because I get ...
4
votes
1answer
135 views
How can I benchmark a PostgreSQL query?
I want to benchmark a query containing an user-defined function I have written for PostgreSQL. Are there any standard ways to run such a benchmark?
I know that timing can be turned on with \timing ...
4
votes
1answer
39 views
How to view the full, flattened query underlying a Postgresql view?
If I have a view on a view on a view (etc.), is there a way to show the full query that this expands/flattens to?
4
votes
2answers
145 views
What happens to the index of a primary key after a DROP CONSTRAINT?
I am running PostgreSQL 9.1.4.
I have a table with many existing rows, and a bunch of other tables with foreign keys pointing to it, for which I am trying to :
1 - Remove the pkey constraint on the ...
4
votes
1answer
164 views
Is the optimisation fence behaviour of a CTE (WITH query) specified in the SQL:2008 standard? If so, where?
I see frequent references to WITH queries (common table expressions, or CTEs) acting as an optimisation fence, where the server isn't permitted to push filters down into the CTE queries, pull common ...
4
votes
1answer
204 views
Is it possible to wrap aggregate functions in Postgres?
Postgres' string_agg(expr, delimiter) function is great. But I would like to have a version that takes a single argument -- the field to aggregate -- and assumes a delimiter of ', ' since that is ...
4
votes
1answer
691 views
How to turn off header only in psql (postgresql)
I'm using PostgreSQL 9.1.X
I am trying to build psql script to print results without a header but including a footer.
http://www.postgresql.org/docs/9.1/static/app-psql.html
From the document ...
4
votes
1answer
193 views
Can you make SSL and SSL-only (hostssl) connections to PGPool?
The new version of PGPool (3.2) has an "SSL" section in the conf file, and directions on how to use it in the manual. However, pool_hba.conf specifically says that PGPool doesn't support SSL (i.e. ...
4
votes
2answers
3k views
Connecting pgAdmin3 to Postgres on Heroku
I'm running a Heroku Postgres database (Database) in assosciation with a Django app. The docs that refer to the add-on are here. I thought this would be a worthwhile question for the community ...
4
votes
1answer
3k views
PostgreSQL Database Data File Integrity Check
When running a PostgreSQL database system how do I know my database as a whole has 100% integrity? Basically how do I know if my data files and pages are all 100% good with no corruption?
In the ...
4
votes
1answer
300 views
Single node parallelism with PL/Proxy?
I have seen some suggest that single node parallelism is possible, but all of the material I have found so far deals with parallelizing across nodes. I am interested in getting large static queries to ...
4
votes
2answers
6k views
How to solve UTF8 invalid byte sequence copy errors on a restore, when the source database is encoded in UTF8?
I was given the task to migrate a PostgreSQL 8.2.x database to another server. To do this I'm using the pgAdmin 1.12.2 (on Ubuntu 11.04 by the way) and using the Backup and Restore using the ...
4
votes
1answer
623 views
PostgreSQL: Corrupt primary key, inconsistent table
While recovering from a cloud failure, I found that some tables on a PostgreSQL database are behaving strangely. These tables are indexed using a primary key, but a pg_dump yielded duplicate fields, ...
4
votes
1answer
251 views
PostgreSQL exit status when running a file
When running PostgreSQL with a single SQL command error codes are returned as expected:
% psql -c "SELECT * FROM AWDASDASDASDAS" my_db
ERROR: relation "awdasdasdasdas" does not exist
LINE 1: SELECT ...
4
votes
1answer
648 views
PostgreSQL on Ubuntu - Using conf files for recovery
I am using PostgreSQL 8.4 on Ubuntu LTS 10.04.02 server version. I use pg_standby to restore the data and for database recovery created a recovery.conf and placed it on /etc/postgresql/8.4/main where ...
4
votes
1answer
292 views
PostgreSQL relation size does not sum up
I have a very large table, with blob fields, called data. I've tried to figure out why it isn't cached well and repeated SELECTs are quite slow:
=> SELECT ...
4
votes
1answer
31 views
How to schedule PostgreSQL replication?
I was reading postgresql replications solution but, even I just starting understaning how it works, another doubt has arrisen.
I'll be using postgres internal replication solution but as far as I ...
4
votes
1answer
67 views
How to simplify a nested SELECT with PostgreSQL arrays?
I'm trying to speed up and simplify a SQL query against an imported OpenStreetMap database (OSM). The database is stored in a PostgreSQL 9.2.4 server.
This OSM import features two particular tables, ...
4
votes
1answer
121 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 ...
4
votes
2answers
171 views
Inner join using an array column
Having trouble indexing and executing a query in O (log n) time.
The query includes an INNER JOIN, an ORDER BY, and an equality operation. If I understand the laws of databases correctly, a query can ...
4
votes
1answer
90 views
How does PostgreSQL physically order new records on disk (after a cluster on primary key)?
Need to know how PostgreSQL orders records on disk. In this case, I would like to take advantage of index combination as stated in the docs, which as I understand uses bitmaps to get matching rows ...
4
votes
1answer
86 views
After Rackspace server creation, PostgreSQL query planner doesn't work as expected
We created an image of one of our database servers in Rackspace. Then, we created a new server using that image, expecting things to work. However, the index performance we have seen seems to be ...
4
votes
2answers
100 views
How to properly configure PostgreSQL RAM usage?
Using PostgreSQL, we have several hundred concurrent processes doing regular database access. We have our data spread out through several database servers, each of which houses a connection pool with ...
4
votes
1answer
131 views
Storing logs on per day basis in PostgreSQL
Requirements here are to store historical logs.
For the sake of simplicity I'll assume this sample scenario where we are selling some products on our website and we need to keep records of sales of ...
4
votes
1answer
208 views
PostgreSQL pg_stat_activity shows COMMIT
We recently replaced our database server with an upgraded machine with 4 x quad core CPUs and 32Gb of ram. We also repurposed our old box to serve as a slave with streaming replication. Both boxes are ...
4
votes
1answer
156 views
PostgreSQL: Recommendation size for shared_buffers
I am rather new to PostgreSQL. I started migrating some MySQL databases to PostgreSQL one month ago.
I am looking for help about the proper memory values for the configuration file.
Currently, I ...
4
votes
1answer
197 views
What is actually happening when postgresql discovers the trigger file and promotes it self from slave to master?
I have a streaming replication setup between two postgres servers (master: server A, slave: server B). I'm wondering what's actually happening under the hood when I touch the trigger file and the ...
4
votes
1answer
123 views
Do isolation levels only apply to SELECTS and not UPDATES? [closed]
Scenario that might demonstrate different behavior for SELECTS depending on the isolation level:
1) 0:00 Thread A runs a query that returns 1000 rows that takes 5 minutes to complete
2) 0:02 Thread B ...
4
votes
1answer
228 views
How do I resolve Postgresql error, 'no collation was derived for column “foo” with collatable type citext'?
Just recently I upgraded from Postgresql 8.4.11 to 9.1.3. I used pg_dump in the process. Now I'm getting an error:
ERROR: no collation was derived for column "vin" with collatable type citext
...
4
votes
2answers
2k views
No NULLs, yet invalid byte sequence for encoding “UTF8”: 0x00
I've spent the last 8 hours trying to import the output of 'mysqldump --compatible=postgresql' into PostgreSQL 8.4.9, and I've read at least 20 different threads here and elesewhere already about this ...
