Tag Info

New answers tagged

3

TL;DR: No, except for some basic cases. Some lock-strength reductions for ALTER TABLE have been added to PostgreSQL 9.5. You can't do anything that requires a full table rewrite without an exclusive lock though, in 9.5 or below. Some operations, like ALTER TABLE ... DROP COLUMN or ALTER TABLE ... ADD COLUMN ... without a DEFAULT and NOT NULL can be done ...


3

For Postgres, adding a column without a default value is essentially a no-op as only the catalog tables get rewritten. Apart from the short exclusive(!) lock there is no performance impact. Although that lock is only held for a very short time, getting the lock on a busy system might be a problem because all open transactions and queries need to be ...


0

The following transformations may help. I have not used the DBMS_XSLPROCESSOR.CLOB2FILE method, but I did use these to migrate an Oracle database from Solaris to Linux. I could not use data pump because of the version of Oracle that they were using and the fact that they used XML data types for column data types. DBMS_METADATA.SET_TRANSFORM_PARAM( ...


2

What @Craig already explained. Plus, since GRANT can grant privileges on multiple objects at once, you can use a single statement without looping: DO $$ BEGIN EXECUTE ( SELECT 'GRANT ALL ON TABLE ' || string_agg (format('%I.%I', table_schema, table_name), ',') || ' TO test' FROM information_schema.tables WHERE table_schema = ...


4

GRANT doesn't take wildcards in table identifiers. You can use ON ALL TABLES IN SCHEMA, but that requires a single schema name. If you want to do things with wildcard pattern table names you will need to use PL/PgSQL's EXECUTE format(...) in a DO block to loop over the information_schema.tables view. See many related answers here on DBA.se and Stack ...


-4

First revoke your database REVOKE CONNECT ON DATABASE your_database FROM PUBLIC; then try with your GRANT query.


0

You could also create the indexes on other tablespaces than the default. These tablespaces could point to disks that are not redundant (just recreate the indexes if they fail), or are on faster arrays. You might also consider partitioning the table using the same criteria as your partial indexes. This would allow for the same speed as the index when ...



Top 50 recent answers are included