Module: Sequel::Postgres::DatabaseMethods

Includes:
UnmodifiedIdentifiers::DatabaseMethods
Included in:
JDBC::Postgres::DatabaseMethods, Database
Defined in:
lib/sequel/adapters/shared/postgres.rb

Constant Summary collapse

FOREIGN_KEY_LIST_ON_DELETE_MAP = 
{'a'=>:no_action, 'r'=>:restrict, 'c'=>:cascade, 'n'=>:set_null, 'd'=>:set_default}.freeze
ON_COMMIT = 
{:drop => 'DROP', :delete_rows => 'DELETE ROWS', :preserve_rows => 'PRESERVE ROWS'}.freeze
SELECT_CUSTOM_SEQUENCE_SQL =

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.

(<<-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
SELECT_PK_SQL =

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.

(<<-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
SELECT_SERIAL_SEQUENCE_SQL =

SQL fragment for getting sequence associated with table's primary key, assuming it was a serial primary key column.

(<<-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

Instance Attribute Summary collapse

Instance Method Summary collapse

Instance Attribute Details

#conversion_procs ⇒ Object (readonly)

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



881
882
883
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 881

def conversion_procs
  @conversion_procs
end

Instance Method Details

#add_conversion_proc(oid, callable = nil, &block) ⇒ Object

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



885
886
887
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 885

def add_conversion_proc(oid, callable=nil, &block)
  conversion_procs[oid] = callable || block
end

#add_named_conversion_proc(name, &block) ⇒ Object

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.



892
893
894
895
896
897
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 892

def add_named_conversion_proc(name, &block)
  unless oid = from(:pg_type).where(:typtype=>['b', 'e'], :typname=>name.to_s).get(:oid)
    raise Error, "No matching type in pg_type for #{name.inspect}"
  end
  add_conversion_proc(oid, block)
end

#alter_property_graph(name, &block) ⇒ Object

Alter the property graph with the given name, supported on PostgreSQL 19+. The block uses a DSL, evaluated by PropertyGraph::Generator::Alter. Example:

DB.alter_property_graph(:my_graph) do
# PropertyGraph::Generator::Alter
add_vertex :companies2
# ALTER PROPERTY GRAPH "my_graph" ADD VERTEX TABLES ("companies2")

add_edge :works_at2 do
  # PropertyGraph::Generator::Edge
  source :people
  destination :companies2
end
# ALTER PROPERTY GRAPH "my_graph" ADD EDGE TABLES
#   ("works_at2" SOURCE "people" DESTINATION "companies2")

drop_vertex_tables [:p2], cascade: true
# ALTER PROPERTY GRAPH "my_graph" DROP VERTEX TABLES ("p2") CASCADE

drop_edge_tables :e2
# ALTER PROPERTY GRAPH "my_graph" DROP EDGE TABLES ("e2")

alter_vertex_table :companies do
  # PropertyGraph::Generator::AlterElement
  add_label :public_company, [:name, :symbol]
  # ALTER PROPERTY GRAPH "my_graph" ALTER VERTEX TABLE "companies"
  #   ADD LABEL "public_company" PROPERTIES ("name", "symbol")

  drop_label :private_company
  # ALTER PROPERTY GRAPH "my_graph" ALTER VERTEX TABLE "companies"
  #   DROP LABEL "private_company"

  add_properties :company, :revenue
  # ALTER PROPERTY GRAPH "my_graph" ALTER VERTEX TABLE "companies"
  #   ALTER LABEL "company" ADD PROPERTIES ("revenue")

  drop_properties :company, :internal_id, cascade: true
  # ALTER PROPERTY GRAPH "my_graph" ALTER VERTEX TABLE "companies"
  #   ALTER LABEL "company" DROP PROPERTIES ("internal_id") CASCADE
end

alter_edge_table :works_at do
  # PropertyGraph::Generator::AlterElement
  add_label :employment
end
# ALTER PROPERTY GRAPH "my_graph" ALTER EDGE TABLE "works_at"
#   ADD LABEL "employment" PROPERTIES ALL COLUMNS

owner_to :new_owner
# ALTER PROPERTY GRAPH "my_graph" OWNER TO "new_owner"
end


950
951
952
953
954
955
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 950

def alter_property_graph(name, &block)
  PropertyGraph::Generator::Alter.new(&block).each do |op|
    execute_ddl(alter_property_graph_op_sql(name, op).freeze)
  end
  nil
end

#check_constraints(table) ⇒ Object

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 deteremine the column symbols.



966
967
968
969
970
971
972
973
974
975
976
977
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 966

def check_constraints(table)
  m = output_identifier_meth

  hash = {}
  _check_constraints_ds.where_each(:conrelid=>regclass_oid(table)) do |row|
    constraint = m.call(row[:constraint])
    entry = hash[constraint] ||= {:definition=>row[:definition], :columns=>[], :validated=>row[:validated], :enforced=>row[:enforced]}
    entry[:columns] << m.call(row[:column]) if row[:column]
  end
  
  hash
end

#commit_prepared_transaction(transaction_id, opts = OPTS) ⇒ Object 



957
958
959
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 957

def commit_prepared_transaction(transaction_id, opts=OPTS)
  run("COMMIT PREPARED #{literal(transaction_id)}".freeze, opts)
end

#convert_serial_to_identity(table, opts = OPTS) ⇒ Object

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

Raises:



997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 997

def convert_serial_to_identity(table, opts=OPTS)
  raise Error, "convert_serial_to_identity is only supported on PostgreSQL 10.2+" unless server_version >= 100002

  server = opts[:server]
  server_hash = server ? {:server=>server} : OPTS
  ds = dataset
  ds = ds.server(server) if server

  raise Error, "convert_serial_to_identity requires superuser permissions" unless ds.get{current_setting('is_superuser')} == 'on'

  table_oid = regclass_oid(table)
  im = input_identifier_meth
  unless column = (opts[:column] || ((sch = schema(table).find{|_, sc| sc[:primary_key] && sc[:auto_increment]}) && sch[0]))
    raise Error, "could not determine column to convert from serial to identity automatically"
  end
  column = im.call(column)

  column_num = ds.from(:pg_attribute).
    where(:attrelid=>table_oid, :attname=>column).
    get(:attnum)

  pg_class = Sequel.cast('pg_class', :regclass)
  res = ds.from(:pg_depend).
    where(:refclassid=>pg_class, :refobjid=>table_oid, :refobjsubid=>column_num, :classid=>pg_class, :objsubid=>0, :deptype=>%w'a i').
    select_map([:objid, Sequel.as({:deptype=>'i'}, :v)])

  case res.length
  when 0
    raise Error, "unable to find related sequence when converting serial to identity"
  when 1
    seq_oid, already_identity = res.first
  else
    raise Error, "more than one linked sequence found when converting serial to identity"
  end

  return if already_identity

  transaction(server_hash) do
    run("ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(column)} DROP DEFAULT".freeze, server_hash)

    ds.from(:pg_depend).
      where(:classid=>pg_class, :objid=>seq_oid, :objsubid=>0, :deptype=>'a').
      update(:deptype=>'i')

    ds.from(:pg_attribute).
      where(:attrelid=>table_oid, :attname=>column).
      update(:attidentity=>'d')
  end

  remove_cached_schema(table)
  nil
end

#create_function(name, definition, opts = OPTS) ⇒ Object

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.



1071
1072
1073
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1071

def create_function(name, definition, opts=OPTS)
  self << create_function_sql(name, definition, opts).freeze
end

#create_language(name, opts = OPTS) ⇒ Object

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.



1082
1083
1084
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1082

def create_language(name, opts=OPTS)
  self << create_language_sql(name, opts).freeze
end

#create_property_graph(name, opts = OPTS, &block) ⇒ Object

Create a property graph in the database, supported on PostgreSQL 19+.

Arguments:

name

Name of the property graph

opts

options hash: :temp :: Create the property graph as a temporary property graph.

The block uses a DSL, with classes under PropertyGraph::Generator:

DB.create_property_graph(:my_graph) do
# PropertyGraph::Generator::Create
vertex :people

vertex Sequel.as(:people, :p), properties: []

vertex Sequel.as(:companies, :c) do
  # PropertyGraph::Generator::Vertex
  key :id
  label :company
  label :c, [:name, (Sequel[:revenue] / 1000).as(:revenue_thousands)]
end

edge :works_at do
  # PropertyGraph::Generator::Edge
  source :people
  destination :c
end

edge Sequel.as(:employment, :e) do
  source :people do
    # PropertyGraph::Generator::Target
    key :person_id
    references :id
  end
  destination :c do
    # PropertyGraph::Generator::Target
    key :company_id
    references :id
  end
  label :employment
end
end
# CREATE PROPERTY GRAPH "my_graph"
# VERTEX TABLES (
#   "people",
#   "people" AS "p" NO PROPERTIES,
#   "companies" AS "c" KEY ("id")
#     LABEL "company" PROPERTIES ALL COLUMNS
#     LABEL "c" PROPERTIES ("name", ("revenue" / 1000) AS "revenue_thousands"))
# EDGE TABLES (
#   "works_at"
#     SOURCE "people"
#     DESTINATION "c",
#   "employment" AS "e"
#     SOURCE KEY ("person_id") REFERENCES "people" ("id")
#     DESTINATION KEY ("company_id") REFERENCES "c" ("id")
#   LABEL "employment" PROPERTIES ALL COLUMNS)


1143
1144
1145
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1143

def create_property_graph(name, opts=OPTS, &block)
  execute_ddl(create_property_graph_sql(name, PropertyGraph::Generator::Create.new(&block), opts))
end

#create_schema(name, opts = OPTS) ⇒ Object

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)



1152
1153
1154
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1152

def create_schema(name, opts=OPTS)
  self << create_schema_sql(name, opts).freeze
end

#create_table(name, options = OPTS, &block) ⇒ Object

Support partitions of tables using the :partition_of option.



1157
1158
1159
1160
1161
1162
1163
1164
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1157

def create_table(name, options=OPTS, &block)
  if options[:partition_of]
    create_partition_of_table_from_generator(name, CreatePartitionOfTableGenerator.new(&block), options)
    return
  end

  super
end

#create_table?(name, options = OPTS, &block) ⇒ Boolean

Support partitions of tables using the :partition_of option.

Returns:

  • (Boolean) 


1167
1168
1169
1170
1171
1172
1173
1174
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1167

def create_table?(name, options=OPTS, &block)
  if options[:partition_of]
    create_table(name, options.merge!(:if_not_exists=>true), &block)
    return
  end

  super
end

#create_trigger(table, name, function, opts = OPTS) ⇒ Object

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



1188
1189
1190
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1188

def create_trigger(table, name, function, opts=OPTS)
  self << create_trigger_sql(table, name, function, opts).freeze
end

#database_type ⇒ Object 



1192
1193
1194
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1192

def database_type
  :postgres
end

#defer_constraints(opts = OPTS) ⇒ Object

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


1211
1212
1213
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1211

def defer_constraints(opts=OPTS)
  _set_constraints(' DEFERRED', opts)
end

#do(code, opts = OPTS) ⇒ Object

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.



1220
1221
1222
1223
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1220

def do(code, opts=OPTS)
  language = opts[:language]
  run "DO #{"LANGUAGE #{literal(language.to_s)} " if language}#{literal(code)}".freeze
end

#drop_function(name, opts = OPTS) ⇒ Object

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.



1231
1232
1233
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1231

def drop_function(name, opts=OPTS)
  self << drop_function_sql(name, opts).freeze
end

#drop_language(name, opts = OPTS) ⇒ Object

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.



1240
1241
1242
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1240

def drop_language(name, opts=OPTS)
  self << drop_language_sql(name, opts).freeze
end

#drop_property_graph(name, opts = OPTS) ⇒ Object

Drops a property graph from the database. Arguments:

name

name of the property graph to drop

opts

options hash: :cascade :: Drop other objects depending on this property_graph. :if_exists :: Don't raise an error if the property graph doesn't exist.



1249
1250
1251
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1249

def drop_property_graph(name, opts=OPTS)
  self << drop_property_graph_sql(name, opts).freeze
end

#drop_schema(name, opts = OPTS) ⇒ Object

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.



1258
1259
1260
1261
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1258

def drop_schema(name, opts=OPTS)
  self << drop_schema_sql(name, opts).freeze
  remove_all_cached_schemas
end

#drop_trigger(table, name, opts = OPTS) ⇒ Object

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.



1269
1270
1271
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1269

def drop_trigger(table, name, opts=OPTS)
  self << drop_trigger_sql(table, name, opts).freeze
end

#foreign_key_list(table, opts = OPTS) ⇒ Object

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.



1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1283

def foreign_key_list(table, opts=OPTS)
  m = output_identifier_meth
  schema, _ = opts.fetch(:schema, schema_and_table(table))

  h = {}
  fklod_map = FOREIGN_KEY_LIST_ON_DELETE_MAP 
  reverse = opts[:reverse]

  (reverse ? _reverse_foreign_key_list_ds : _foreign_key_list_ds).where_each(Sequel[:cl][:oid]=>regclass_oid(table)) do |row|
    if reverse
      key = [row[:schema], row[:table], row[:name]]
    else
      key = row[:name]
    end

    if r = h[key]
      r[:columns] << m.call(row[:column])
      r[:key] << m.call(row[:refcolumn])
    else
      entry = h[key] = {
        :name=>m.call(row[:name]),
        :columns=>[m.call(row[:column])],
        :key=>[m.call(row[:refcolumn])],
        :on_update=>fklod_map[row[:on_update]],
        :on_delete=>fklod_map[row[:on_delete]],
        :deferrable=>row[:deferrable],
        :validated=>row[:validated],
        :enforced=>row[:enforced],
        :table=>schema ? SQL::QualifiedIdentifier.new(m.call(row[:schema]), m.call(row[:table])) : m.call(row[:table]),
      }

      unless schema
        # If not combining schema information into the :table entry
        # include it as a separate entry.
        entry[:schema] = m.call(row[:schema])
      end
    end
  end

  h.values
end

#freeze ⇒ Object 



1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1325

def freeze
  server_version
  supports_prepared_transactions?
  _schema_ds
  _select_serial_sequence_ds
  _select_custom_sequence_ds
  _select_pk_ds
  _indexes_ds
  _check_constraints_ds
  _foreign_key_list_ds
  _reverse_foreign_key_list_ds
  @conversion_procs.freeze
  super
end

#graph_table(property_graph_name, initial_vertex_label, initial_vertex_opts = OPTS) ⇒ Object

Return a PropertyGraph::Table instance for a property graph search (a GRAPH_TABLE clause for a SELECT query). Supported on PostgreSQL 19+.

Arguments:

property_graph_name

The property graph to query

initial_vertex_label

The label restriction for the initial vertex for the graph pattern (can be nil for no label, or an array or set for restricting to one of multiple labels).

initial_vertex_opts

The options for the initial vertex, see PropertyGraph::Table#link for available options.

The returned instance should be further modified by calling methods on it, using a similar approach to how datasets work, where the methods return a modified copy of the receiver. The available methods:

link

Add a bidirectional link to a new element (vertex or edge)

to

Add a directional link from the last element to the new element

from

Add a direciton link from the new element to last element

columns

Replace the columns the graph table returns

add_columns

Append to the columns the graph table returns.

See PropertyGraph::Table for the details of these methods and the arguments and options they support. Note that for a graph table to be usable in a query, it must return at least one column, and the last element in the graph pattern must be a vertex.

gt = DB.graph_table(:pgn, :iv)
# Not yet usable, does not return any columns

# Set columns for graph table
gt = gt.columns(:c, Sequel[1].as(:d))
# GRAPH_TABLE ("pgn" MATCH (IS "iv") COLUMNS ("c", 1 AS "d"))

# Adds directional link to edge, since last (initial) element was a vertex
gt = gt.link(:e1)
# GRAPH_TABLE ("pgn" MATCH (IS "iv")-[IS "e1"] COLUMNS ("c", 1 AS "d"))

# Adds directional link from edge to vertex, since last element was an edge
gt = gt.to(:v2)
# GRAPH_TABLE ("pgn" MATCH (IS "iv")-[IS "e1"]->(IS "v2") COLUMNS ("c", 1 AS "d"))

# Adds bidirection link from vertex to vertex (overriding the default)
gt = gt.link(:v3, vertex: true)
# GRAPH_TABLE ("pgn" MATCH (IS "iv")-[IS "e1"]->(IS "v2")-(IS "v3") COLUMNS ("c", 1 AS "d"))

# Adds directional link from new edge to last vertex, since last element was an vertex.
# Sets graph pattern variable name and uses it in a WHERE clause for the added element.
gt = gt.from(:e2, var: :a2, where: {Sequel[:a2][:c] => 1})
# GRAPH_TABLE ("pgn" MATCH (IS "iv")-[IS "e1"]->(IS "v2")-(IS "v3")
#   <-["a2" IS "e2" WHERE ("a2"."c" = 1)] COLUMNS ("c", 1 AS "d"))

# Can use nil as a label for no label restriction, both with and without a variable name
gt = gt.to(nil).to(nil, var: :a3)
# GRAPH_TABLE ("pgn" MATCH (IS "iv")-[IS "e1"]->(IS "v2")-(IS "v3")
#   <-["a2" IS "e2" WHERE ("a2"."c" = 1)]->[]->("a3") COLUMNS ("c", 1 AS "d"))

# Can restrict to a one of a set of labels
gt = gt.from([:x, :y], var: :a6)
# GRAPH_TABLE ("pgn" MATCH (IS "iv")-[IS "e1"]->(IS "v2")-(IS "v3")
#   <-["a2" IS "e2" WHERE ("a2"."c" = 1)]->[]->("a3")->["a6" IS "x"|"y"] COLUMNS ("c", 1 AS "d"))

# Add column(s) to the graph table
gt = gt.add_columns(:y)
# GRAPH_TABLE ("pgn" MATCH (IS "iv")-[IS "e1"]->(IS "v2")-(IS "v3")
#   <-["a2" IS "e2" WHERE ("a2"."c" = 1)]->[]->("a3")->["a6" IS "x"|"y"]
#   COLUMNS ("c", 1 AS "d", "y"))

DB.from(gt)
# SELECT * FROM GRAPH_TABLE (...)

DB.from(:x).cross_join(gt)
# SELECT * FROM "x" CROSS JOIN GRAPH_TABLE (...)


1412
1413
1414
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1412

def graph_table(property_graph_name, initial_vertex_label, initial_vertex_opts=OPTS)
  PropertyGraph::Table.create(property_graph_name, initial_vertex_label, initial_vertex_opts)
end

#immediate_constraints(opts = OPTS) ⇒ Object

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


1430
1431
1432
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1430

def immediate_constraints(opts=OPTS)
  _set_constraints(' IMMEDIATE', opts)
end

#indexes(table, opts = OPTS) ⇒ Object

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.



1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1440

def indexes(table, opts=OPTS)
  m = output_identifier_meth
  cond = {Sequel[:tab][:oid]=>regclass_oid(table, opts)}
  cond[:indpred] = nil unless opts[:include_partial]

  case opts[:invalid]
  when true, :only
    cond[:indisvalid] = false
  when :include
    # nothing
  else
    cond[:indisvalid] = true
  end

  indexes = {}
  _indexes_ds.where_each(cond) do |r|
    i = indexes[m.call(r[:name])] ||= {:columns=>[], :unique=>r[:unique], :deferrable=>r[:deferrable]}
    i[:columns] << m.call(r[:column])
  end
  indexes
end

#locks ⇒ Object

Dataset containing all current database locks



1463
1464
1465
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1463

def locks
  dataset.from(:pg_class).join(:pg_locks, :relation=>:relfilenode).select{[pg_class[:relname], Sequel::SQL::ColumnAll.new(:pg_locks)]}
end

#notify(channel, opts = OPTS) ⇒ Object

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.



1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1473

def notify(channel, opts=OPTS)
  sql = String.new
  sql << "NOTIFY "
  dataset.send(:identifier_append, sql, channel)
  if payload = opts[:payload]
    sql << ", "
    dataset.literal_append(sql, payload.to_s)
  end
  execute_ddl(sql, opts)
end

#primary_key(table, opts = OPTS) ⇒ Object

Return primary key for the given table.



1485
1486
1487
1488
1489
1490
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1485

def primary_key(table, opts=OPTS)
  quoted_table = quote_schema_table(table)
  Sequel.synchronize{return @primary_keys[quoted_table] if @primary_keys.has_key?(quoted_table)}
  value = _select_pk_ds.where_single_value(Sequel[:pg_class][:oid] => regclass_oid(table, opts))
  Sequel.synchronize{@primary_keys[quoted_table] = value}
end

#primary_key_sequence(table, opts = OPTS) ⇒ Object

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



1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1493

def primary_key_sequence(table, opts=OPTS)
  quoted_table = quote_schema_table(table)
  Sequel.synchronize{return @primary_key_sequences[quoted_table] if @primary_key_sequences.has_key?(quoted_table)}
  cond = {Sequel[:t][:oid] => regclass_oid(table, opts)}
  value = if pks = _select_serial_sequence_ds.first(cond)
    literal(SQL::QualifiedIdentifier.new(pks[:schema], pks[:sequence]))
  elsif pks = _select_custom_sequence_ds.first(cond)
    literal(SQL::QualifiedIdentifier.new(pks[:schema], LiteralString.new(pks[:sequence])))
  end

  Sequel.synchronize{@primary_key_sequences[quoted_table] = value} if value
end

#property_graphs(opts = OPTS, &block) ⇒ Object

Array of symbols specifying property graphs in the current database. The dataset used is yielded to the block if one is provided, otherwise, an array of symbols of property graph names is returned. Supported on PostgreSQL 19+, will be an empty array on lower versions.

Options: :qualify :: Return the property graph names as Sequel::SQL::QualifiedIdentifier instances, using the schema the property graph is located in as the qualifier. :schema :: The schema to search :server :: The server to use



1516
1517
1518
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1516

def property_graphs(opts=OPTS, &block)
  pg_class_relname('g', opts, &block)
end

#refresh_view(name, opts = OPTS) ⇒ Object

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


1542
1543
1544
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1542

def refresh_view(name, opts=OPTS)
  run "REFRESH MATERIALIZED VIEW#{' CONCURRENTLY' if opts[:concurrently]} #{quote_schema_table(name)}".freeze
end

#rename_property_graph(old_name, new_name) ⇒ Object

Rename a property graph.

DB.rename_property_graph(:x, :y)
# ALTER PROPERTY GRAPH x RENAME TO y


1524
1525
1526
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1524

def rename_property_graph(old_name, new_name)
  execute_ddl("ALTER PROPERTY GRAPH #{literal(old_name)} RENAME TO #{literal(new_name)}".freeze)
end

#rename_schema(name, new_name) ⇒ Object

Rename a schema in the database. Arguments:

name

Current name of the schema

opts

New name for the schema



1531
1532
1533
1534
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1531

def rename_schema(name, new_name)
  self << rename_schema_sql(name, new_name).freeze
  remove_all_cached_schemas
end

#reset_primary_key_sequence(table) ⇒ Object

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



1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1548

def reset_primary_key_sequence(table)
  return unless seq = primary_key_sequence(table)
  pk = SQL::Identifier.new(primary_key(table))
  db = self
  s, t = schema_and_table(table)
  table = Sequel.qualify(s, t) if s

  if server_version >= 100000
    seq_ds = metadata_dataset.from(:pg_sequence).where(:seqrelid=>regclass_oid(LiteralString.new(seq.freeze)))
    increment_by = :seqincrement
    min_value = :seqmin
  # :nocov:
  else
    seq_ds = metadata_dataset.from(LiteralString.new(seq))
    increment_by = :increment_by
    min_value = :min_value
  # :nocov:
  end

  get{setval(seq, db[table].select(coalesce(max(pk)+seq_ds.select(increment_by), seq_ds.select(min_value))), false)}
end

#rollback_prepared_transaction(transaction_id, opts = OPTS) ⇒ Object 



1570
1571
1572
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1570

def rollback_prepared_transaction(transaction_id, opts=OPTS)
  run("ROLLBACK PREPARED #{literal(transaction_id)}".freeze, opts)
end

#serial_primary_key_options ⇒ Object

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



1576
1577
1578
1579
1580
1581
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1576

def serial_primary_key_options
  # :nocov:
  auto_increment_key = server_version >= 100002 ? :identity : :serial
  # :nocov:
  {:primary_key => true, auto_increment_key => true, :type=>Integer}
end

#server_version(server = nil) ⇒ Object

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



1584
1585
1586
1587
1588
1589
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1584

def server_version(server=nil)
  return @server_version if @server_version
  ds = dataset
  ds = ds.server(server) if server
  @server_version = swallow_database_error{ds.with_sql("SELECT CAST(current_setting('server_version_num') AS integer) AS v").single_value} || 0
end

#set_property_graph_schema(old_name, new_name, opts = OPTS) ⇒ Object

Change the schema for a property graph. Options: :if_exists :: Use the IF EXISTS clause to not raise an error if the property graph does not exist.

DB.set_property_graph_schema(:x, :y)
# ALTER PROPERTY GRAPH x SET SCHEMA y


1597
1598
1599
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1597

def set_property_graph_schema(old_name, new_name, opts=OPTS)
  execute_ddl("ALTER PROPERTY GRAPH#{" IF EXISTS" if opts[:if_exists]} #{literal(old_name)} SET SCHEMA #{literal(new_name)}".freeze)
end

#supports_create_table_if_not_exists? ⇒ Boolean

PostgreSQL supports CREATE TABLE IF NOT EXISTS on 9.1+

Returns:

  • (Boolean) 


1602
1603
1604
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1602

def supports_create_table_if_not_exists?
  server_version >= 90100
end

#supports_deferrable_constraints? ⇒ Boolean

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

Returns:

  • (Boolean) 


1607
1608
1609
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1607

def supports_deferrable_constraints?
  server_version >= 90000
end

#supports_deferrable_foreign_key_constraints? ⇒ Boolean

PostgreSQL supports deferrable foreign key constraints.

Returns:

  • (Boolean) 


1612
1613
1614
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1612

def supports_deferrable_foreign_key_constraints?
  true
end

#supports_drop_table_if_exists? ⇒ Boolean

PostgreSQL supports DROP TABLE IF EXISTS

Returns:

  • (Boolean) 


1617
1618
1619
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1617

def supports_drop_table_if_exists?
  true
end

#supports_partial_indexes? ⇒ Boolean

PostgreSQL supports partial indexes.

Returns:

  • (Boolean) 


1622
1623
1624
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1622

def supports_partial_indexes?
  true
end

#supports_prepared_transactions? ⇒ Boolean

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

Returns:

  • (Boolean) 


1633
1634
1635
1636
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1633

def supports_prepared_transactions?
  return @supports_prepared_transactions if defined?(@supports_prepared_transactions)
  @supports_prepared_transactions = self['SHOW max_prepared_transactions'].get.to_i > 0
end

#supports_savepoints? ⇒ Boolean

PostgreSQL supports savepoints

Returns:

  • (Boolean) 


1639
1640
1641
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1639

def supports_savepoints?
  true
end

#supports_transaction_isolation_levels? ⇒ Boolean

PostgreSQL supports transaction isolation levels

Returns:

  • (Boolean) 


1644
1645
1646
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1644

def supports_transaction_isolation_levels?
  true
end

#supports_transactional_ddl? ⇒ Boolean

PostgreSQL supports transaction DDL statements.

Returns:

  • (Boolean) 


1649
1650
1651
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1649

def supports_transactional_ddl?
  true
end

#supports_trigger_conditions? ⇒ Boolean

PostgreSQL 9.0+ supports trigger conditions.

Returns:

  • (Boolean) 


1627
1628
1629
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1627

def supports_trigger_conditions?
  server_version >= 90000
end

#tables(opts = OPTS, &block) ⇒ Object

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



1662
1663
1664
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1662

def tables(opts=OPTS, &block)
  pg_class_relname(['r', 'p'], opts, &block)
end

#type_supported?(type) ⇒ Boolean

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

Returns:

  • (Boolean) 


1668
1669
1670
1671
1672
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1668

def type_supported?(type)
  Sequel.synchronize{return @supported_types[type] if @supported_types.has_key?(type)}
  supported = from(:pg_type).where(:typtype=>'b', :typname=>type.to_s).count > 0
  Sequel.synchronize{return @supported_types[type] = supported}
end

#values(v) ⇒ Object

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

Raises:



1681
1682
1683
1684
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1681

def values(v)
  raise Error, "Cannot provide an empty array for values" if v.empty?
  @default_dataset.clone(:values=>v)
end

#views(opts = OPTS) ⇒ Object

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



1694
1695
1696
1697
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1694

def views(opts=OPTS)
  relkind = opts[:materialized] ? 'm' : 'v'
  pg_class_relname(relkind, opts)
end

#with_advisory_lock(lock_id, opts = OPTS) ⇒ Object

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.



1710
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1710

def with_advisory_lock(lock_id, opts=OPTS)
  ds = dataset
  if server = opts[:server]
    ds = ds.server(server)
  end

  synchronize(server) do |c|
    begin
      if opts[:wait]
        ds.get{pg_advisory_lock(lock_id)}
        locked = true
      else
        unless locked = ds.get{pg_try_advisory_lock(lock_id)}
          raise AdvisoryLockError, "unable to acquire advisory lock #{lock_id.inspect}"
        end
      end

      yield
    ensure
      ds.get{pg_advisory_unlock(lock_id)} if locked
    end
  end
end