module Sequel::Postgres::DatabaseMethods

  1. lib/sequel/adapters/shared/postgres.rb

Constants

DATABASE_ERROR_REGEXPS = [ # Add this check first, since otherwise it's possible for users to control # which exception class is generated. [/invalid input syntax/, DatabaseError], [/duplicate key value violates unique constraint/, UniqueConstraintViolation], [/violates foreign key constraint/, ForeignKeyConstraintViolation], [/violates check constraint/, CheckConstraintViolation], [/violates not-null constraint/, NotNullConstraintViolation], [/conflicting key value violates exclusion constraint/, ExclusionConstraintViolation], [/could not serialize access/, SerializationFailure], [/could not obtain lock on row in relation/, DatabaseLockTimeout], ].freeze  
FOREIGN_KEY_LIST_ON_DELETE_MAP = {'a'=>:no_action, 'r'=>:restrict, 'c'=>:cascade, 'n'=>:set_null, 'd'=>:set_default}.freeze  
MAX_DATE = Date.new(5874897, 12, 31)  
MAX_TIMESTAMP = (Time.utc(294277) - Rational(1, 1000000)).freeze  
MIN_DATE = Date.new(-4713, 11, 24)  
MIN_TIMESTAMP = Time.utc(-4713, 11, 24).freeze  
ON_COMMIT = {:drop => 'DROP', :delete_rows => 'DELETE ROWS', :preserve_rows => 'PRESERVE ROWS'}.freeze  
SELECT_CUSTOM_SEQUENCE_SQL = (<<-end_sql SELECT name.nspname AS "schema", CASE WHEN split_part(pg_get_expr(def.adbin, attr.attrelid), '''', 2) ~ '.' THEN substr(split_part(pg_get_expr(def.adbin, attr.attrelid), '''', 2), strpos(split_part(pg_get_expr(def.adbin, attr.attrelid), '''', 2), '.')+1) ELSE split_part(pg_get_expr(def.adbin, attr.attrelid), '''', 2) END AS "sequence" FROM pg_class t JOIN pg_namespace name ON (t.relnamespace = name.oid) JOIN pg_attribute attr ON (t.oid = attrelid) JOIN pg_attrdef def ON (adrelid = attrelid AND adnum = attnum) JOIN pg_constraint cons ON (conrelid = adrelid AND adnum = conkey[1]) WHERE cons.contype = 'p' AND pg_get_expr(def.adbin, attr.attrelid) ~* 'nextval' end_sql ).strip.gsub(/\s+/, ' ').freeze  

SQL fragment for custom sequences (ones not created by serial primary key), Returning the schema and literal form of the sequence name, by parsing the column defaults table.
SELECT_PK_SQL = (<<-end_sql SELECT pg_attribute.attname AS pk FROM pg_class, pg_attribute, pg_index, pg_namespace WHERE pg_class.oid = pg_attribute.attrelid AND pg_class.relnamespace = pg_namespace.oid AND pg_class.oid = pg_index.indrelid AND pg_index.indkey[0] = pg_attribute.attnum AND pg_index.indisprimary = 't' end_sql ).strip.gsub(/\s+/, ' ').freeze  

SQL fragment for determining primary key column for the given table. Only returns the first primary key if the table has a composite primary key.
SELECT_SERIAL_SEQUENCE_SQL = (<<-end_sql SELECT name.nspname AS "schema", seq.relname AS "sequence" FROM pg_class seq, pg_attribute attr, pg_depend dep, pg_namespace name, pg_constraint cons, pg_class t WHERE seq.oid = dep.objid AND seq.relnamespace = name.oid AND seq.relkind = 'S' AND attr.attrelid = dep.refobjid AND attr.attnum = dep.refobjsubid AND attr.attrelid = cons.conrelid AND attr.attnum = cons.conkey[1] AND attr.attrelid = t.oid AND cons.contype = 'p' end_sql ).strip.gsub(/\s+/, ' ').freeze  

SQL fragment for getting sequence associated with table’s primary key, assuming it was a serial primary key column.
TYPTYPE_METHOD_MAP = { 'c' => :schema_composite_type, 'e' => :schema_enum_type, 'r' => :schema_range_type, 'm' => :schema_multirange_type, }  
VALID_CLIENT_MIN_MESSAGES = %w'DEBUG5 DEBUG4 DEBUG3 DEBUG2 DEBUG1 LOG NOTICE WARNING ERROR FATAL PANIC'.freeze.each(&:freeze)  

Attributes

conversion_procs [R]

A hash of conversion procs, keyed by type integer (oid) and having callable values for the conversion proc for that type.

Public Instance methods

add_conversion_proc(oid, callable=nil, &block)

Set a conversion proc for the given oid. The callable can be passed either as a argument or a block.

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
356 def add_conversion_proc(oid, callable=nil, &block)
357   conversion_procs[oid] = callable || block
358 end
add_named_conversion_proc(name, &block)

Add a conversion proc for a named type, using the given block. This should be used for types without fixed OIDs, which includes all types that are not included in a default PostgreSQL installation.

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
363 def add_named_conversion_proc(name, &block)
364   unless oid = from(:pg_type).where(:typtype=>['b', 'e'], :typname=>name.to_s).get(:oid)
365     raise Error, "No matching type in pg_type for #{name.inspect}"
366   end
367   add_conversion_proc(oid, block)
368 end
check_constraints(table)

A hash of metadata for CHECK constraints on the table. Keys are CHECK constraint name symbols. Values are hashes with the following keys:

:definition

An SQL fragment for the definition of the constraint

:columns

An array of column symbols for the columns referenced in the constraint, can be an empty array if the database cannot determine the column symbols.

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
379 def check_constraints(table)
380   m = output_identifier_meth
381 
382   hash = {}
383   _check_constraints_ds.where_each(:conrelid=>regclass_oid(table)) do |row|
384     constraint = m.call(row[:constraint])
385     entry = hash[constraint] ||= {:definition=>row[:definition], :columns=>[], :validated=>row[:validated], :enforced=>row[:enforced]}
386     entry[:columns] << m.call(row[:column]) if row[:column]
387   end
388   
389   hash
390 end
commit_prepared_transaction(transaction_id, opts=OPTS)
[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
370 def commit_prepared_transaction(transaction_id, opts=OPTS)
371   run("COMMIT PREPARED #{literal(transaction_id)}".freeze, opts)
372 end
convert_serial_to_identity(table, opts=OPTS)

Convert the first primary key column in the table from being a serial column to being an identity column. If the column is already an identity column, assume it was already converted and make no changes.

Only supported on PostgreSQL 10.2+, since on those versions Sequel will use identity columns instead of serial columns for auto incrementing primary keys. Only supported when running as a superuser, since regular users cannot modify system tables, and there is no way to keep an existing sequence when changing an existing column to be an identity column.

This method can raise an exception in at least the following cases where it may otherwise succeed (there may be additional cases not listed here):

  • The serial column was added after table creation using PostgreSQL <7.3

  • A regular index also exists on the column (such an index can probably be dropped as the primary key index should suffice)

Options:

:column

Specify the column to convert instead of using the first primary key column

:server

Run the SQL on the given server

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
410 def convert_serial_to_identity(table, opts=OPTS)
411   raise Error, "convert_serial_to_identity is only supported on PostgreSQL 10.2+" unless server_version >= 100002
412 
413   server = opts[:server]
414   server_hash = server ? {:server=>server} : OPTS
415   ds = dataset
416   ds = ds.server(server) if server
417 
418   raise Error, "convert_serial_to_identity requires superuser permissions" unless ds.get{current_setting('is_superuser')} == 'on'
419 
420   table_oid = regclass_oid(table)
421   im = input_identifier_meth
422   unless column = (opts[:column] || ((sch = schema(table).find{|_, sc| sc[:primary_key] && sc[:auto_increment]}) && sch[0]))
423     raise Error, "could not determine column to convert from serial to identity automatically"
424   end
425   column = im.call(column)
426 
427   column_num = ds.from(:pg_attribute).
428     where(:attrelid=>table_oid, :attname=>column).
429     get(:attnum)
430 
431   pg_class = Sequel.cast('pg_class', :regclass)
432   res = ds.from(:pg_depend).
433     where(:refclassid=>pg_class, :refobjid=>table_oid, :refobjsubid=>column_num, :classid=>pg_class, :objsubid=>0, :deptype=>%w'a i').
434     select_map([:objid, Sequel.as({:deptype=>'i'}, :v)])
435 
436   case res.length
437   when 0
438     raise Error, "unable to find related sequence when converting serial to identity"
439   when 1
440     seq_oid, already_identity = res.first
441   else
442     raise Error, "more than one linked sequence found when converting serial to identity"
443   end
444 
445   return if already_identity
446 
447   transaction(server_hash) do
448     run("ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(column)} DROP DEFAULT".freeze, server_hash)
449 
450     ds.from(:pg_depend).
451       where(:classid=>pg_class, :objid=>seq_oid, :objsubid=>0, :deptype=>'a').
452       update(:deptype=>'i')
453 
454     ds.from(:pg_attribute).
455       where(:attrelid=>table_oid, :attname=>column).
456       update(:attidentity=>'d')
457   end
458 
459   remove_cached_schema(table)
460   nil
461 end
create_function(name, definition, opts=OPTS)

Creates the function in the database. Arguments:

name

name of the function to create

definition

string definition of the function, or object file for a dynamically loaded C function.

opts

options hash:

:args

function arguments, can be either a symbol or string specifying a type or an array of 1-3 elements:

1

argument data type

2

argument name

3

argument mode (e.g. in, out, inout)

:behavior

Should be IMMUTABLE, STABLE, or VOLATILE. PostgreSQL assumes VOLATILE by default.

:parallel

The thread safety attribute of the function. Should be SAFE, UNSAFE, RESTRICTED. PostgreSQL assumes UNSAFE by default.

:cost

The estimated cost of the function, used by the query planner.

:language

The language the function uses. SQL is the default.

:link_symbol

For a dynamically loaded see function, the function’s link symbol if different from the definition argument.

:returns

The data type returned by the function. If you are using OUT or INOUT argument modes, this is ignored. Otherwise, if this is not specified, void is used by default to specify the function is not supposed to return a value.

:rows

The estimated number of rows the function will return. Only use if the function returns SETOF something.

:security_definer

Makes the privileges of the function the same as the privileges of the user who defined the function instead of the privileges of the user who runs the function. There are security implications when doing this, see the PostgreSQL documentation.

:set

Configuration variables to set while the function is being run, can be a hash or an array of two pairs. search_path is often used here if :security_definer is used.

:strict

Makes the function return NULL when any argument is NULL.

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
484 def create_function(name, definition, opts=OPTS)
485   self << create_function_sql(name, definition, opts).freeze
486 end
create_language(name, opts=OPTS)

Create the procedural language in the database. Arguments:

name

Name of the procedural language (e.g. plpgsql)

opts

options hash:

:handler

The name of a previously registered function used as a call handler for this language.

:replace

Replace the installed language if it already exists (on PostgreSQL 9.0+).

:trusted

Marks the language being created as trusted, allowing unprivileged users to create functions using this language.

:validator

The name of previously registered function used as a validator of functions defined in this language.

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
495 def create_language(name, opts=OPTS)
496   self << create_language_sql(name, opts).freeze
497 end
create_schema(name, opts=OPTS)

Create a schema in the database. Arguments:

name

Name of the schema (e.g. admin)

opts

options hash:

:if_not_exists

Don’t raise an error if the schema already exists (PostgreSQL 9.3+)

:owner

The owner to set for the schema (defaults to current user if not specified)

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
504 def create_schema(name, opts=OPTS)
505   self << create_schema_sql(name, opts).freeze
506 end
create_table(name, options=OPTS, &block)

Support partitions of tables using the :partition_of option.

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
509 def create_table(name, options=OPTS, &block)
510   if options[:partition_of]
511     create_partition_of_table_from_generator(name, CreatePartitionOfTableGenerator.new(&block), options)
512     return
513   end
514 
515   super
516 end
create_table?(name, options=OPTS, &block)

Support partitions of tables using the :partition_of option.

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
519 def create_table?(name, options=OPTS, &block)
520   if options[:partition_of]
521     create_table(name, options.merge!(:if_not_exists=>true), &block)
522     return
523   end
524 
525   super
526 end
create_trigger(table, name, function, opts=OPTS)

Create a trigger in the database. Arguments:

table

the table on which this trigger operates

name

the name of this trigger

function

the function to call for this trigger, which should return type trigger.

opts

options hash:

:after

Calls the trigger after execution instead of before.

:args

An argument or array of arguments to pass to the function.

:each_row

Calls the trigger for each row instead of for each statement.

:events

Can be :insert, :update, :delete, or an array of any of those. Calls the trigger whenever that type of statement is used. By default, the trigger is called for insert, update, or delete.

:replace

Replace the trigger with the same name if it already exists (PostgreSQL 14+).

:when

A filter to use for the trigger

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
540 def create_trigger(table, name, function, opts=OPTS)
541   self << create_trigger_sql(table, name, function, opts).freeze
542 end
database_type()
[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
544 def database_type
545   :postgres
546 end
defer_constraints(opts=OPTS)

For constraints that are deferrable, defer constraints until transaction commit. Options:

:constraints

An identifier of the constraint, or an array of identifiers for constraints, to apply this change to specific constraints.

:server

The server/shard on which to run the query.

Examples:

DB.defer_constraints
# SET CONSTRAINTS ALL DEFERRED

DB.defer_constraints(constraints: [:c1, Sequel[:sc][:c2]])
# SET CONSTRAINTS "c1", "sc"."s2" DEFERRED
[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
563 def defer_constraints(opts=OPTS)
564   _set_constraints(' DEFERRED', opts)
565 end
do(code, opts=OPTS)

Use PostgreSQL’s DO syntax to execute an anonymous code block. The code should be the literal code string to use in the underlying procedural language. Options:

:language

The procedural language the code is written in. The PostgreSQL default is plpgsql. Can be specified as a string or a symbol.

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
572 def do(code, opts=OPTS)
573   language = opts[:language]
574   run "DO #{"LANGUAGE #{literal(language.to_s)} " if language}#{literal(code)}".freeze
575 end
drop_function(name, opts=OPTS)

Drops the function from the database. Arguments:

name

name of the function to drop

opts

options hash:

:args

The arguments for the function. See create_function_sql.

:cascade

Drop other objects depending on this function.

:if_exists

Don’t raise an error if the function doesn’t exist.

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
583 def drop_function(name, opts=OPTS)
584   self << drop_function_sql(name, opts).freeze
585 end
drop_language(name, opts=OPTS)

Drops a procedural language from the database. Arguments:

name

name of the procedural language to drop

opts

options hash:

:cascade

Drop other objects depending on this function.

:if_exists

Don’t raise an error if the function doesn’t exist.

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
592 def drop_language(name, opts=OPTS)
593   self << drop_language_sql(name, opts).freeze
594 end
drop_schema(name, opts=OPTS)

Drops a schema from the database. Arguments:

name

name of the schema to drop

opts

options hash:

:cascade

Drop all objects in this schema.

:if_exists

Don’t raise an error if the schema doesn’t exist.

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
601 def drop_schema(name, opts=OPTS)
602   self << drop_schema_sql(name, opts).freeze
603   remove_all_cached_schemas
604 end
drop_trigger(table, name, opts=OPTS)

Drops a trigger from the database. Arguments:

table

table from which to drop the trigger

name

name of the trigger to drop

opts

options hash:

:cascade

Drop other objects depending on this function.

:if_exists

Don’t raise an error if the function doesn’t exist.

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
612 def drop_trigger(table, name, opts=OPTS)
613   self << drop_trigger_sql(table, name, opts).freeze
614 end
foreign_key_list(table, opts=OPTS)

Return full foreign key information using the pg system tables, including :name, :on_delete, :on_update, and :deferrable entries in the hashes.

Supports additional options:

:reverse

Instead of returning foreign keys in the current table, return foreign keys in other tables that reference the current table.

:schema

Set to true to have the :table value in the hashes be a qualified identifier. Set to false to use a separate :schema value with the related schema. Defaults to whether the given table argument is a qualified identifier.

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
626 def foreign_key_list(table, opts=OPTS)
627   m = output_identifier_meth
628   schema, _ = opts.fetch(:schema, schema_and_table(table))
629 
630   h = {}
631   fklod_map = FOREIGN_KEY_LIST_ON_DELETE_MAP 
632   reverse = opts[:reverse]
633 
634   (reverse ? _reverse_foreign_key_list_ds : _foreign_key_list_ds).where_each(Sequel[:cl][:oid]=>regclass_oid(table)) do |row|
635     if reverse
636       key = [row[:schema], row[:table], row[:name]]
637     else
638       key = row[:name]
639     end
640 
641     if r = h[key]
642       r[:columns] << m.call(row[:column])
643       r[:key] << m.call(row[:refcolumn])
644     else
645       entry = h[key] = {
646         :name=>m.call(row[:name]),
647         :columns=>[m.call(row[:column])],
648         :key=>[m.call(row[:refcolumn])],
649         :on_update=>fklod_map[row[:on_update]],
650         :on_delete=>fklod_map[row[:on_delete]],
651         :deferrable=>row[:deferrable],
652         :validated=>row[:validated],
653         :enforced=>row[:enforced],
654         :table=>schema ? SQL::QualifiedIdentifier.new(m.call(row[:schema]), m.call(row[:table])) : m.call(row[:table]),
655       }
656 
657       unless schema
658         # If not combining schema information into the :table entry
659         # include it as a separate entry.
660         entry[:schema] = m.call(row[:schema])
661       end
662     end
663   end
664 
665   h.values
666 end
freeze()
[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
668 def freeze
669   server_version
670   supports_prepared_transactions?
671   _schema_ds
672   _select_serial_sequence_ds
673   _select_custom_sequence_ds
674   _select_pk_ds
675   _indexes_ds
676   _check_constraints_ds
677   _foreign_key_list_ds
678   _reverse_foreign_key_list_ds
679   @conversion_procs.freeze
680   super
681 end
immediate_constraints(opts=OPTS)

Immediately apply deferrable constraints.

:constraints

An identifier of the constraint, or an array of identifiers for constraints, to apply this change to specific constraints.

:server

The server/shard on which to run the query.

Examples:

DB.immediate_constraints
# SET CONSTRAINTS ALL IMMEDIATE

DB.immediate_constraints(constraints: [:c1, Sequel[:sc][:c2]])
# SET CONSTRAINTS "c1", "sc"."s2" IMMEDIATE
[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
697 def immediate_constraints(opts=OPTS)
698   _set_constraints(' IMMEDIATE', opts)
699 end
indexes(table, opts=OPTS)

Use the pg_* system tables to determine indexes on a table. Options:

:include_partial

Set to true to include partial indexes

:invalid

Set to true or :only to only return invalid indexes. Set to :include to also return both valid and invalid indexes. When not set or other value given, does not return invalid indexes.

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
707 def indexes(table, opts=OPTS)
708   m = output_identifier_meth
709   cond = {Sequel[:tab][:oid]=>regclass_oid(table, opts)}
710   cond[:indpred] = nil unless opts[:include_partial]
711 
712   case opts[:invalid]
713   when true, :only
714     cond[:indisvalid] = false
715   when :include
716     # nothing
717   else
718     cond[:indisvalid] = true
719   end
720 
721   indexes = {}
722   _indexes_ds.where_each(cond) do |r|
723     i = indexes[m.call(r[:name])] ||= {:columns=>[], :unique=>r[:unique], :deferrable=>r[:deferrable]}
724     i[:columns] << m.call(r[:column])
725   end
726   indexes
727 end
locks()

Dataset containing all current database locks

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
730 def locks
731   dataset.from(:pg_class).join(:pg_locks, :relation=>:relfilenode).select{[pg_class[:relname], Sequel::SQL::ColumnAll.new(:pg_locks)]}
732 end
notify(channel, opts=OPTS)

Notifies the given channel. See the PostgreSQL NOTIFY documentation. Options:

:payload

The payload string to use for the NOTIFY statement. Only supported in PostgreSQL 9.0+.

:server

The server to which to send the NOTIFY statement, if the sharding support is being used.

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
740 def notify(channel, opts=OPTS)
741   sql = String.new
742   sql << "NOTIFY "
743   dataset.send(:identifier_append, sql, channel)
744   if payload = opts[:payload]
745     sql << ", "
746     dataset.literal_append(sql, payload.to_s)
747   end
748   execute_ddl(sql, opts)
749 end
primary_key(table, opts=OPTS)

Return primary key for the given table.

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
752 def primary_key(table, opts=OPTS)
753   quoted_table = quote_schema_table(table)
754   Sequel.synchronize{return @primary_keys[quoted_table] if @primary_keys.has_key?(quoted_table)}
755   value = _select_pk_ds.where_single_value(Sequel[:pg_class][:oid] => regclass_oid(table, opts))
756   Sequel.synchronize{@primary_keys[quoted_table] = value}
757 end
primary_key_sequence(table, opts=OPTS)

Return the sequence providing the default for the primary key for the given table.

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
760 def primary_key_sequence(table, opts=OPTS)
761   quoted_table = quote_schema_table(table)
762   Sequel.synchronize{return @primary_key_sequences[quoted_table] if @primary_key_sequences.has_key?(quoted_table)}
763   cond = {Sequel[:t][:oid] => regclass_oid(table, opts)}
764   value = if pks = _select_serial_sequence_ds.first(cond)
765     literal(SQL::QualifiedIdentifier.new(pks[:schema], pks[:sequence]))
766   elsif pks = _select_custom_sequence_ds.first(cond)
767     literal(SQL::QualifiedIdentifier.new(pks[:schema], LiteralString.new(pks[:sequence])))
768   end
769 
770   Sequel.synchronize{@primary_key_sequences[quoted_table] = value} if value
771 end
refresh_view(name, opts=OPTS)

Refresh the materialized view with the given name.

DB.refresh_view(:items_view)
# REFRESH MATERIALIZED VIEW items_view
DB.refresh_view(:items_view, concurrently: true)
# REFRESH MATERIALIZED VIEW CONCURRENTLY items_view
[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
787 def refresh_view(name, opts=OPTS)
788   run "REFRESH MATERIALIZED VIEW#{' CONCURRENTLY' if opts[:concurrently]} #{quote_schema_table(name)}".freeze
789 end
rename_schema(name, new_name)

Rename a schema in the database. Arguments:

name

Current name of the schema

opts

New name for the schema

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
776 def rename_schema(name, new_name)
777   self << rename_schema_sql(name, new_name).freeze
778   remove_all_cached_schemas
779 end
reset_primary_key_sequence(table)

Reset the primary key sequence for the given table, basing it on the maximum current value of the table’s primary key.

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
793 def reset_primary_key_sequence(table)
794   return unless seq = primary_key_sequence(table)
795   pk = SQL::Identifier.new(primary_key(table))
796   db = self
797   s, t = schema_and_table(table)
798   table = Sequel.qualify(s, t) if s
799 
800   if server_version >= 100000
801     seq_ds = metadata_dataset.from(:pg_sequence).where(:seqrelid=>regclass_oid(LiteralString.new(seq.freeze)))
802     increment_by = :seqincrement
803     min_value = :seqmin
804   # simplecov:disable
805   else
806     seq_ds = metadata_dataset.from(LiteralString.new(seq))
807     increment_by = :increment_by
808     min_value = :min_value
809   # simplecov:enable
810   end
811 
812   get{setval(seq, db[table].select(coalesce(max(pk)+seq_ds.select(increment_by), seq_ds.select(min_value))), false)}
813 end
rollback_prepared_transaction(transaction_id, opts=OPTS)
[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
815 def rollback_prepared_transaction(transaction_id, opts=OPTS)
816   run("ROLLBACK PREPARED #{literal(transaction_id)}".freeze, opts)
817 end
serial_primary_key_options()

PostgreSQL uses SERIAL psuedo-type instead of AUTOINCREMENT for managing incrementing primary keys.

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
821 def serial_primary_key_options
822   # simplecov:disable
823   auto_increment_key = server_version >= 100002 ? :identity : :serial
824   # simplecov:enable
825   {:primary_key => true, auto_increment_key => true, :type=>Integer}
826 end
server_version(server=nil)

The version of the PostgreSQL server, used for determining capability.

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
829 def server_version(server=nil)
830   return @server_version if @server_version
831   ds = dataset
832   ds = ds.server(server) if server
833   @server_version = swallow_database_error{ds.with_sql("SELECT CAST(current_setting('server_version_num') AS integer) AS v").single_value} || 0
834 end
supports_create_table_if_not_exists?()

PostgreSQL supports CREATE TABLE IF NOT EXISTS on 9.1+

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
837 def supports_create_table_if_not_exists?
838   server_version >= 90100
839 end
supports_deferrable_constraints?()

PostgreSQL 9.0+ supports some types of deferrable constraints beyond foreign key constraints.

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
842 def supports_deferrable_constraints?
843   server_version >= 90000
844 end
supports_deferrable_foreign_key_constraints?()

PostgreSQL supports deferrable foreign key constraints.

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
847 def supports_deferrable_foreign_key_constraints?
848   true
849 end
supports_drop_table_if_exists?()

PostgreSQL supports DROP TABLE IF EXISTS

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
852 def supports_drop_table_if_exists?
853   true
854 end
supports_partial_indexes?()

PostgreSQL supports partial indexes.

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
857 def supports_partial_indexes?
858   true
859 end
supports_prepared_transactions?()

PostgreSQL supports prepared transactions (two-phase commit) if max_prepared_transactions is greater than 0.

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
868 def supports_prepared_transactions?
869   return @supports_prepared_transactions if defined?(@supports_prepared_transactions)
870   @supports_prepared_transactions = self['SHOW max_prepared_transactions'].get.to_i > 0
871 end
supports_savepoints?()

PostgreSQL supports savepoints

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
874 def supports_savepoints?
875   true
876 end
supports_transaction_isolation_levels?()

PostgreSQL supports transaction isolation levels

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
879 def supports_transaction_isolation_levels?
880   true
881 end
supports_transactional_ddl?()

PostgreSQL supports transaction DDL statements.

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
884 def supports_transactional_ddl?
885   true
886 end
supports_trigger_conditions?()

PostgreSQL 9.0+ supports trigger conditions.

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
862 def supports_trigger_conditions?
863   server_version >= 90000
864 end
tables(opts=OPTS, &block)

Array of symbols specifying table names in the current database. The dataset used is yielded to the block if one is provided, otherwise, an array of symbols of table names is returned.

Options:

:qualify

Return the tables as Sequel::SQL::QualifiedIdentifier instances, using the schema the table is located in as the qualifier.

:schema

The schema to search

:server

The server to use

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
897 def tables(opts=OPTS, &block)
898   pg_class_relname(['r', 'p'], opts, &block)
899 end
type_supported?(type)

Check whether the given type name string/symbol (e.g. :hstore) is supported by the database.

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
903 def type_supported?(type)
904   Sequel.synchronize{return @supported_types[type] if @supported_types.has_key?(type)}
905   supported = from(:pg_type).where(:typtype=>'b', :typname=>type.to_s).count > 0
906   Sequel.synchronize{return @supported_types[type] = supported}
907 end
values(v)

Creates a dataset that uses the VALUES clause:

DB.values([[1, 2], [3, 4]])
# VALUES ((1, 2), (3, 4))

DB.values([[1, 2], [3, 4]]).order(:column2).limit(1, 1)
# VALUES ((1, 2), (3, 4)) ORDER BY column2 LIMIT 1 OFFSET 1
[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
916 def values(v)
917   raise Error, "Cannot provide an empty array for values" if v.empty?
918   @default_dataset.clone(:values=>v)
919 end
views(opts=OPTS)

Array of symbols specifying view names in the current database.

Options:

:materialized

Return materialized views

:qualify

Return the views as Sequel::SQL::QualifiedIdentifier instances, using the schema the view is located in as the qualifier.

:schema

The schema to search

:server

The server to use

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
929 def views(opts=OPTS)
930   relkind = opts[:materialized] ? 'm' : 'v'
931   pg_class_relname(relkind, opts)
932 end
with_advisory_lock(lock_id, opts=OPTS)

Attempt to acquire an exclusive advisory lock with the given lock_id (which should be a 64-bit integer). If successful, yield to the block, then release the advisory lock when the block exits. If unsuccessful, raise a Sequel::AdvisoryLockError.

DB.with_advisory_lock(1347){DB.get(1)}
# SELECT pg_try_advisory_lock(1357) LIMIT 1
# SELECT 1 AS v LIMIT 1
# SELECT pg_advisory_unlock(1357) LIMIT 1

Options:

:wait

Do not raise an error, instead, wait until the advisory lock can be acquired.

[show source] 
    # File lib/sequel/adapters/shared/postgres.rb
945 def with_advisory_lock(lock_id, opts=OPTS)
946   ds = dataset
947   if server = opts[:server]
948     ds = ds.server(server)
949   end
950 
951   synchronize(server) do |c|
952     begin
953       if opts[:wait]
954         ds.get{pg_advisory_lock(lock_id)}
955         locked = true
956       else
957         unless locked = ds.get{pg_try_advisory_lock(lock_id)}
958           raise AdvisoryLockError, "unable to acquire advisory lock #{lock_id.inspect}"
959         end
960       end
961 
962       yield
963     ensure
964       ds.get{pg_advisory_unlock(lock_id)} if locked
965     end
966   end
967 end